SCSM 2012 SP1 Data Warehouse Jobs failed with error message of <xxxx>vw is not updatable because a partitioning column was not found.

Wyatt Wong 116

I found 4 data warehouse jobs have a Failed status. After further checking, the error messages mentioned "UNION ALL view '<xxxx>vw' is not updatable because a partitioning column was not found." How can I resolve the issue ?

Load.Common
Transform.Common
Load.CMDWDataMart
Load.OMDWDataMart

Song, Michael 6 Reputation points

2020-12-03T00:49:49.313+00:00

I am also having the same issue with SCSM2016 1807
Georges Mouawad 11 Reputation points

2020-12-03T11:03:55.883+00:00

we are facing the same issue with SCSM 2012 R2 CU9

I tried to restore databases, even restore the VMs with no success.

Accepted answer

D B 111 Reputation points

2020-12-03T23:38:36.76+00:00
steps provided by MS Support...I did these with all the job schedules disabled...

on the datawarehouse SQL server...

select max(datekey) from DWRepository..DateDim --should be 20501230
select max(datekey) from DwDataMart..DateDim --should be 20501230
select max(datekey) from CMDwDataMart..DateDim --should be 20501230
select max(datekey) from OMDwDataMart..DateDim --should be 20501230

If your errors are caused by this issue, the above queries will return 20201230

If date keys are from Dec 2020, please use the below steps:

Backup all DW DBs

Add additional date keys until 2050
Exec DWRepository.dbo.PopulateDateDim '20210101','20501231'
Exec DwDataMart.dbo.PopulateDateDim '20210101','20501231'
Exec CMDwDataMart.dbo.PopulateDateDim '20210101','20501231'
Exec OMDwDataMart.dbo.PopulateDateDim '20210101','20501231'

rebuild the constraints on all 2021 fact tables by using the following script Run the following on DWRepository, DWDatamart, CMDwDatamart, OMDwDatamart

declare @objName sysname, @newChk as nvarchar(max)
declare c cursor local FORWARD_ONLY READ_ONLY for
select name from sys.sysobjects where xtype='C' and name like '%_2020_Dec_Chk' escape '\' order by 1
open c; while 1=1 begin; fetch c into @objName if @@Fetch _STATUS<>0 break;
set @objName = replace(@objName,'_2020_Dec_','_2021_Jan_')
set @objName = replace(@objName,'_Chk','')
set @newChk=replace('ALTER TABLE [dbo].[XYZFact_2021_Jan] WITH CHECK ADD CONSTRAINT [XYZFact_2021_Jan_Chk] CHECK (([DateKey]>=(20210101)));ALTER TABLE [dbo].[XYZFact_2021_Jan] CHECK CONSTRAINT [XYZFact_2021_Jan_Chk];','XYZFact_2021_Jan',@objName)
exec sp_executesql @newChk
end; close c; deallocate c;

Restart Microsoft Monitoring Agent

After this I was able to manually start each of the failed jobs, waited for completion, then enabled my job schedules.
Please sign in to rate this answer.

6 people found this answer helpful.
Andrew Gordon 11 Reputation points

2020-12-04T01:00:54.287+00:00

This looks like it is working for me. A million, billion thank yous.

Andrew Gordon 11 Reputation points

2020-12-04T03:05:00.183+00:00

Yes, that means that you have to do the steps listed above. I got the same results that you did, performed the SQL scripts and then processed. Everything is going through perfectly again. Yea!!!

Zakaria Muhammad 161 Reputation points

2020-12-04T03:36:27.463+00:00

what does this XYZFact means on this line, "set @newChk=replace('ALTER TABLE [dbo].[XYZFact_2021_Jan]"?

i don't have any table like XYZFact_2021...

Wyatt Wong 116 Reputation points

2020-12-04T03:42:58.363+00:00

I also want to know that. I have executed the above script for 4 databases DWRepository, DWDatamart, CMDwDatamart, OMDwDatamart and got no errors.

Wyatt Wong 116 Reputation points

2020-12-04T04:05:44.193+00:00

I have executed the steps provided by DB-7386 and now checking the result.

D B 111 Reputation points

2020-12-04T05:10:36.517+00:00

I'm no SQL expert, but it appears to be a variable for each job name that needs updated. If you add these lines before the exec sp_executesql @newChk line you will see...if you are concerned, remove the sp_executesql line...

Print (@objName)
Print (@newchk)

first example from mine:
ALTER TABLE [dbo].[ActivityRelatesToActionLogFact_2021_Jan] WITH CHECK ADD CONSTRAINT [ActivityRelatesToActionLogFact_2021_Jan_Chk] CHECK (([DateKey]>=(20210101)));ALTER TABLE [dbo].[ActivityRelatesToActionLogFact_2021_Jan] CHECK CONSTRAINT [ActivityRelatesToActionLogFact_2021_Jan_Chk];

Wyatt Wong 116 Reputation points

2020-12-04T05:28:00.697+00:00

Here is the result before execution of the above SQL statements:

Here is the result after execution of the above above SQL statements and re-run of the SCDW Jobs. The BatchId of Load.Common, Transform.Common, Load.CMDWDataMart and Load.OMDWDataMart have successfully changed to a new value, which means the the previous BatchId have successfully completed.

Wyatt Wong 116 Reputation points

2020-12-04T08:04:42.463+00:00

I believed ZakariaMuhammad-8648 was asking a different thing. He mentioned that he could not find any table called XYZFact_2021_Jan. I'll check if I can find out more information.

ST1285 11 Reputation points

2020-12-04T12:06:46.817+00:00

Thanks for sharing, but we're getting absolutely no luck with this. We run the above in SQL, get 'Command Completed Successfully', but still get the 'partitioning column was not found' error on the transform.common and the three Load.x jobs.

However, there are 31 days in Dec - should the top bits not be 20501231?

Pablo Schlegel 6 Reputation points

2020-12-04T15:52:11.11+00:00

Solve!, Thanks

mohammad saad 21 Reputation points

2020-12-07T10:14:19.473+00:00

Thanks, It worked for us as well, we have been facing this issue since 01-DEC-2020, and now it is resolved.

Thank you!

Wyatt Wong 116 Reputation points

2020-12-08T04:05:34.16+00:00

I modified the above SQL script as follows:

USE [DWRepository]

declare @objName sysname, @newChk as nvarchar(max)
declare c cursor local FORWARD_ONLY READ_ONLY for
select name from sys.sysobjects where xtype='C' and name like '%_2020_Dec_Chk' escape '\' order by 1
open c; while 1=1 begin; fetch c into @objName if @@Fetch _STATUS<>0 break;
set @objName = replace(@objName,'2020_Dec','2021_Jan')
set @objName = replace(@objName,'_Chk','')
set @newChk=replace('ALTER TABLE [dbo].[XYZFact_2021_Jan] WITH CHECK ADD CONSTRAINT [XYZFact_2021_Jan_Chk] CHECK (([DateKey]>=(20210101)));ALTER TABLE [dbo].[XYZFact_2021_Jan] CHECK CONSTRAINT [XYZFact_2021_Jan_Chk];','XYZFact_2021_Jan',@objName)
print @newChk
end; close c; deallocate c;

Please find a sample output of the @newChk variable as follows:

Jaime Esquivel 6 Reputation points

2020-12-09T22:09:31.303+00:00

This just worked for us! Thank you!!

Wyatt Wong 116 Reputation points

2020-12-10T00:52:25.523+00:00

Have you run the following SQL statements

Exec DWRepository.dbo.PopulateDateDim '20210101','20501231'
Exec DwDataMart.dbo.PopulateDateDim '20210101','20501231'
Exec CMDwDataMart.dbo.PopulateDateDim '20210101','20501231'
Exec OMDwDataMart.dbo.PopulateDateDim '20210101','20501231'

and then execute the SQL script ?

According to @D B , you need to run the SQL script in each of the DWRepository, DWDatamart, CMDwDatamart, OMDwDatamart databases. You can add the USE [database name] command first before executing the SQL script

You also need to restart the System Center Health Service in the SCSM Data Warehouse management server and re-run each SCDW Job of Load.Common, Transform.Common, Load.CMDWDataMart and Load.OMDWDataMart

Camilo Andres Vanegas Ramirez 11 Reputation points

2020-12-30T21:52:11.837+00:00

Muchas gracias, me funciono, la ejecución de los job's se ejecutaron de forma correcta, de nuevo muchas gracias

Salim Assaf 6 Reputation points

2020-12-31T19:14:20.06+00:00

To everyone looking at this, you may want to double check your dates... Dec 31, 2020 does not exist in the DateDim tables therefore the below should likely be adjusted as it will cause you problems.

Exec DWRepository.dbo.PopulateDateDim '20201231','20501231'
Exec DwDataMart.dbo.PopulateDateDim '20201231','20501231'
Exec CMDwDataMart.dbo.PopulateDateDim '20201231','20501231'
Exec OMDwDataMart.dbo.PopulateDateDim '20201231','20501231'

Camilo Andres Vanegas Ramirez 11 Reputation points

2021-01-04T13:50:25.597+00:00

realice el procedimiento el día 31 de diciembre y fue satisfactorio, sin embargo validando el día de hoy nuevamente se presentan las mismas fallas, agradezco su colaboración si hace falta ejecutar algo adicional, gracias

C Sharp Conner 1 Reputation point

2021-01-07T00:19:35.577+00:00

Still having issue on SCSM 2012 R2 UR9. Error slightly different from yours, believed to have happened Dec 30th. Error 4457.

Operation Manager Error 4457 - The attempted insert or update of the partitioned view failed because the value of the partitioning column does not belong to any of the partitions.

Eventually the TransformEntityManagedTypeFact module fails as a result with same 4457 error…. Details say On Step 5 Inserting Facts (Which are relationships) … the others say "Inserting into destination fact"

I think it stands to reason the "value" is bad data like a NULL being entered for a relationship.

Hypothesis: The LIVE Database has bad data in the dbo.relationship table and when it is given to the ARCHIVE, the ARCHIVE is unable to insert this bad data (like NULLs) for Primary Keys or something like that...

Wyatt Wong 116 Reputation points

2021-01-07T05:35:03.747+00:00

Can you post the screen output of "Get-SCDWJob" and share to us ?

Wyatt Wong 116 Reputation points

2021-01-07T05:37:39.04+00:00

I wrote a SQL script to list out all SCSM DW Jobs but excluding the Completed Jobs. Here it is:

USE [DWStagingAndConfig]

SELECT p.ProcessName, w.WorkItemId, w.ProcessModuleId, pm.VertexName AS [ModuleName], w.BatchId, w.StatusId, s.StatusName, w.RetryCount, w.ErrorCount, w.ErrorSummary, w.InsertTime, w.TakenTime, w.UpdateTime, w.NotToBePickedBefore, w.CustomInfo
FROM Infra.[Process] p INNER JOIN Infra.[Batch] b ON p.ProcessId = b.[ProcessId]
INNER JOIN Infra.WorkItem w ON b.BatchId = w.[BatchId]
INNER JOIN Infra.[Status] s ON w.StatusId = s.StatusId
INNER JOIN Infra.[ProcessModule] pm ON w.ProcessModuleId = pm.ProcessModuleId
WHERE b.StatusId <> 6 AND
w.StatusId <> 6
ORDER BY StatusId DESC, p.ProcessName ASC

@Jim Finke @Zakaria Muhammad @mohammad saad @Salim Assaf

Wyatt Wong 116 Reputation points

2021-01-07T05:42:29.533+00:00

An enhanced version include a list of commented items.

To check the failed SCSM DW Jobs, simply comment out the "w.StatusId <> 6' and uncomment "w.StatusId = 2"

To look for a specific SCSM DW Jobs, simply uncomment one of the "-- AND ProcessName = 'XXXXXXXXXX'" entry

@Jim Finke @Zakaria Muhammad @mohammad saad @Salim Assaf

mohammad saad 21 Reputation points

2021-01-07T06:13:07.113+00:00

great!!!

Thank you

Wyatt Wong 116 Reputation points

2021-01-07T10:33:14.44+00:00

The script listed out all the SCSM DW Jobs (including their modules) with BatchId as well as the StatusId and the Status Description. I created the script so that I can find out the BatchId of each SCSM DW Jobs more quickly than using the Powershell command Get-SCDWJob

Daniel Luck 1 Reputation point

2021-01-09T21:23:08.703+00:00

Dear All,

Please help, i have run the this fix and it was working up until 30th December 2020. All jobs fail now and there is not reporting after 30th December. I have tried all items in this forum and none seems to work.

This is the fix I did. So anything after this date doesn't show.

I am very keen to get his fixed. If someone has a script to fix it? It appears that 31st December is not in the database?

Thanks

Daniel

Fix: DB-7386

Daniel Luck 1 Reputation point

2021-01-09T21:24:02.46+00:00

Dear All,

Please help, i have run the this fix and it was working up until 30th December 2020. All jobs fail now and there is not reporting after 30th December. I have tried all items in this forum and none seems to work.

This is the fix I did. So anything after this date doesn't show.

I am very keen to get his fixed. If someone has a script to fix it? It appears that 31st December is not in the database?

Thanks

Daniel

Fix: DB-7386

James Johnson 1 Reputation point

2021-02-03T19:01:13.19+00:00

Is this supposed to create the xyzFact_2021_JAN tables? I've tried running your modified script (thanks for posting it) but I still don't have any Jan tables and I keep getting the partitioning column error on the Transform.Common job.

Wyatt Wong 116 Reputation points

2021-02-04T03:11:31.74+00:00

There is no need to create any tables. You can send e-mail to me at <wyattwong @ outlook . com> for further assistance.
Sign in to comment

38 additional answers

Zakaria Muhammad 161 Reputation points

2020-12-08T21:46:20.883+00:00
I ran this but seems like the 2021_Jan Fact tables is missing the Range compare to 2020_Jan

USE [DWDataMart]
ALTER TABLE [dbo].[EntityManagedTypeFact_2020_Jan] WITH CHECK ADD CONSTRAINT [EntityManagedTypeFact_2020_Jan_Chk] CHECK (([DateKey]>=(20200101) AND [DateKey]<=(20200131)))
ALTER TABLE [dbo].[EntityManagedTypeFact_2021_Jan] WITH CHECK ADD CONSTRAINT [EntityManagedTypeFact_2021_Jan_Chk] CHECK (([DateKey]>=(20210101)))

USE [DWRepository]
ALTER TABLE [dbo].[EntityManagedTypeFact_2021_Jan] WITH CHECK ADD CONSTRAINT [EntityManagedTypeFact_2021_Jan_Chk] CHECK (([DateKey]>=(20210101)))
ALTER TABLE [dbo].[EntityManagedTypeFact_2020_Jan] WITH CHECK ADD CONSTRAINT [EntityManagedTypeFact_2020_Jan_Chk] CHECK (([DateKey]>=(20200101) AND [DateKey]<=(20200131)))

I will wait for the next month to see if these are fixed. Also if we see the PopulateDateDim it still have

@StartDay smalldatetime = **'20000101',** @EndDay smalldatetime = **'20201231'**
Please sign in to rate this answer.
Anonymous

2020-12-09T06:00:09.403+00:00

I think that it's normal state, that oldest and newest table have always "open" boundaries.
Boundaries for January will be changed or corrected next month when the table for February will be created.

The oldest table in my database
ALTER TABLE [dbo].[ActivityStageDurationFact_2018_Aug] WITH CHECK ADD CONSTRAINT [ActivityStageDurationFact_2018_Aug_Chk] CHECK (([DateKey]<=(20180831)))

The newest table in my database
ALTER TABLE [dbo].[ActivityStageDurationFact_2021_Jan] WITH CHECK ADD CONSTRAINT [ActivityStageDurationFact_2021_Jan_Chk] CHECK (([DateKey]>=(20210101)))

mohammad saad 21 Reputation points

2020-12-31T10:58:33.817+00:00

USE [DWRepository]
GO

ALTER TABLE [dbo].[EntityManagedTypeFact_2020_Dec] WITH CHECK ADD CONSTRAINT
[EntityManagedTypeFact_2020_Dec_Chk] CHECK (([DateKey]>=(20201201) AND [DateKey]<=(20201230)))
GO

ALTER TABLE [dbo].[EntityManagedTypeFact_2020_Dec] CHECK CONSTRAINT [EntityManagedTypeFact_2020_Dec_Chk]
GO

above is my entry and my DW job is giving me below error, could you please help me to resolve this?

Module name: TransformEntityRelatesToEntityFact
Message: ErrorNumber="4457" Message="The attempted insert or update of the partitioned view failed because the value of the partitioning column does not belong to any of the partitions." Severity="16" State="1" ProcedureName="TransformEntityRelatesToEntityFactProc" LineNumber="305" Task="Step 8: Inserting new records into destination fact"

mohammad saad 21 Reputation points

2020-12-31T11:14:16.813+00:00

SELECT max(datekey)
FROM [dbo].[EntityManagedTypeFact_2020_dec]

20201230
Sign in to comment
Jim Finke 1 Reputation point

2020-12-09T16:06:06.603+00:00

After running the accepted answer, my jobs were still failing. I modified the script as follows:

declare @objName sysname, @newChk as nvarchar(max), anonymous userChk as nvarchar(max)
declare c cursor local FORWARD_ONLY READ_ONLY for
select name from sys.sysobjects where xtype='C' and name like '%_2020_Dec_Chk' escape '\' order by 1
open c; while 1=1 begin; fetch c into @objName if @@Fetch _STATUS<>0 break;
set @objName = replace(@objName,'2020_Dec','2021_Jan')
set @objName = replace(@objName,'_Chk','')
set anonymous userChk=replace('ALTER TABLE [dbo].[XYZFact_2021_Jan] DROP CONSTRAINT [XYZFact_2021_Jan_Chk];','XYZFact_2021_Jan',@objName)
set @newChk=replace('ALTER TABLE [dbo].[XYZFact_2021_Jan] WITH CHECK ADD CONSTRAINT [XYZFact_2021_Jan_Chk] CHECK (([DateKey]>=(20210101) AND [DateKey]<=(20210131)));ALTER TABLE [dbo].[XYZFact_2021_Jan] CHECK CONSTRAINT [XYZFact_2021_Jan_Chk];','XYZFact_2021_Jan',@objName)
print anonymous userChk
print @newChk
print '';
end; close c; deallocate c;

I had to add the drop constraint because they were created when I originally tried this. I added an end date to the Jan constraint of 20210131. Also, I used the print command that @Wyatt Wong suggested instead of the execute command. I copied the results and executed them in another query window.

Next, I made the following changes to set the Dec constraints to an end date of 20201231 instead of 20201230:

--December
declare @objName sysname, @newChk as nvarchar(max), anonymous userChk as nvarchar(max)
declare c cursor local FORWARD_ONLY READ_ONLY for
select name from sys.sysobjects where xtype='C' and name like '%_2020_Dec_Chk' escape '\' order by 1
open c; while 1=1 begin; fetch c into @objName if @@Fetch _STATUS<>0 break;
--set @objName = replace(@objName,'2020_Dec','2021_Jan')
set @objName = replace(@objName,'_Chk','')
set anonymous userChk=replace('ALTER TABLE [dbo].[XYZFact_2021_Jan] DROP CONSTRAINT [XYZFact_2021_Jan_Chk];','XYZFact_2021_Jan',@objName)
set @newChk=replace('ALTER TABLE [dbo].[XYZFact_2021_Jan] WITH CHECK ADD CONSTRAINT [XYZFact_2021_Jan_Chk] CHECK (([DateKey]>=(20201201) AND [DateKey]<=(20201231)));ALTER TABLE [dbo].[XYZFact_2021_Jan] CHECK CONSTRAINT [XYZFact_2021_Jan_Chk];','XYZFact_2021_Jan',@objName)
print anonymous userChk
print @newChk
print '';
end; close c; deallocate c;

After this, my jobs ran successfully.
Please sign in to rate this answer.
Wyatt Wong 116 Reputation points

2020-12-10T01:17:51.513+00:00

Very good then. When I run the following SQL statements, I also got the dates of 20201230 and I did not make any changes at all.

select max(datekey) from DWRepository..DateDim
select max(datekey) from DwDataMart..DateDim
select max(datekey) from CMDwDataMart..DateDim
select max(datekey) from OMDwDataMart..DateDim

@D B already mentioned that you need to execute the following SQL statements to add additional date keys until 2050:

Exec DWRepository.dbo.PopulateDateDim '20210101','20501231'
Exec DwDataMart.dbo.PopulateDateDim '20210101','20501231'
Exec CMDwDataMart.dbo.PopulateDateDim '20210101','20501231'
Exec OMDwDataMart.dbo.PopulateDateDim '20210101','20501231'

After executing the above SQL statements, when you run the above 4 SELECT SQL statements again, you should got the result of 20501231

mohammad saad 21 Reputation points

2021-01-04T06:02:25.833+00:00

Hi,

I need to update constraint for jan 2021 with upper and lower limit and i am trying to execute your script but getting below error, could you please help in this?

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'anonymous'.
Msg 137, Level 15, State 2, Line 4
Must declare the scalar variable "@objName".
Msg 137, Level 15, State 2, Line 5
Must declare the scalar variable "@objName".
Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@objName".
Msg 195, Level 15, State 7, Line 7
'anonymous' is not a recognized SET option.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@objName".
Msg 128, Level 15, State 1, Line 9
The name "anonymous" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Msg 137, Level 15, State 2, Line 10
Must declare the scalar variable "@newChk".

Wyatt Wong 116 Reputation points

2021-01-05T09:52:33.767+00:00

--December
declare @objName sysname, @newChk as nvarchar(max), @userChk as nvarchar(max)
declare c cursor local FORWARD_ONLY READ_ONLY for
select name from sys.sysobjects where xtype='C' and name like '%_2020_Dec_Chk' escape '\' order by 1
open c; while 1=1 begin; fetch c into @objName if @@Fetch _STATUS<>0 break;
--set @objName = replace(@objName,'2020_Dec','2021_Jan')
set @objName = replace(@objName,'_Chk','')
set @userChk=replace('ALTER TABLE [dbo].[XYZFact_2021_Jan] DROP CONSTRAINT [XYZFact_2021_Jan_Chk];','XYZFact_2021_Jan',@objName)
set @newChk=replace('ALTER TABLE [dbo].[XYZFact_2021_Jan] WITH CHECK ADD CONSTRAINT [XYZFact_2021_Jan_Chk] CHECK (([DateKey]>=(20201201) AND [DateKey]<=(20201231)));ALTER TABLE [dbo].[XYZFact_2021_Jan] CHECK CONSTRAINT [XYZFact_2021_Jan_Chk];','XYZFact_2021_Jan',@objName)
print @userChk
print @newChk
print '';
end; close c; deallocate c;

mohammad saad 21 Reputation points

2021-01-06T07:29:00.36+00:00

Thanks but this script is for dec-31, i was getting error when executing jan script with begin and end date so for that i have manually added them. below is the snap of Jimfanke script snap for jan-21 but it is not working for me

set anonymous userChk=replace('ALTER TABLE [dbo].[XYZFact_2021_Jan] DROP CONSTRAINT [XYZFact_2021_Jan_Chk];','XYZFact_2021_Jan',@objName)
set @newChk=replace('ALTER TABLE [dbo].[XYZFact_2021_Jan] WITH CHECK ADD CONSTRAINT [XYZFact_2021_Jan_Chk] CHECK (([DateKey]>=(20210101) AND [DateKey]<=(20210131)));ALTER TABLE [dbo].[XYZFact_2021_Jan] CHECK CONSTRAINT [XYZFact_2021_Jan_Chk];','XYZFact_2021_Jan',@objName)

Thanks
Sign in to comment
Shumail Khalid 1 Reputation point

2020-12-31T08:59:54.773+00:00

Hi,

It was worked previously after executig script shared by DB-7386 , but today again we face the same problem after 12 am.
may be it is because of 31/12/2020 date? any resolution for this issue? below is the error we are facing in the DW job error log

Module name: TransformEntityRelatesToEntityFact
Message: ErrorNumber="4457" Message="The attempted insert or update of the partitioned view failed because the value of the partitioning column does not belong to any of the partitions." Severity="16" State="1" ProcedureName="TransformEntityRelatesToEntityFactProc" LineNumber="305" Task="Step 8: Inserting new records into destination fact"

Stack: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at Microsoft.SystemCenter.Warehouse.Utility.SqlHelper.ExecuteReader(SqlConnection sqlCon, CommandType cmdType, String cmdText, SqlParameter[] parameters)
at Microsoft.SystemCenter.Warehouse.Etl.StoredProcedure.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser)
at Microsoft.SystemCenter.Warehouse.Etl.TransformModule.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser, Int32 batchSize)
at Microsoft.SystemCenter.Warehouse.Etl.TransformModule.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser)

Thanks
Please sign in to rate this answer.
Salim Assaf 6 Reputation points

2020-12-31T21:31:33.243+00:00

Ran into this issue today as well:
1 - Your DateDim tables are likely missing today's date 12/31/2020. You can rerun the below commands to fix those tables. Ignore any errors you likely get.

Exec DWRepository.dbo.PopulateDateDim '20201231','20501231'
Exec DwDataMart.dbo.PopulateDateDim '20201231','20501231'
Exec CMDwDataMart.dbo.PopulateDateDim '20201231','20501231'
Exec OMDwDataMart.dbo.PopulateDateDim '20201231','20501231'

Fix the tables that are giving errors, so in your case the table EntityRelatesToEntityFact_2020_Dec is likely missing the 12/31/2020 date in the constraint.

Resume the failed job

mohammad saad 21 Reputation points

2021-01-01T08:47:35.277+00:00

Thanks salim for resolution,

when we ran the above query its give duplicate key violation error till 20501230.

error on execute query

Msg 2627, Level 14, State 1, Procedure PopulateDateDim, Line 43 [Batch Start Line 2]
Violation of PRIMARY KEY constraint 'PK__DateDim__40DF45E307F6335A'. Cannot insert duplicate key in object 'dbo.DateDim'. The duplicate key value is (20210101).

error on job

Module name: LoadCMDWDataMartComputerHostsOperatingSystemFact
Message: UNION ALL view 'CMDWDataMart.dbo.ComputerHostsOperatingSystemFactvw' is not updatable because a partitioning column was not found.

Module name: TransformEntityRelatesToEntityFact
Message: UNION ALL view 'DWRepository.dbo.EntityRelatesToEntityFactvw' is not updatable because a partitioning column was not found.

date still showing 30 in below query:

select max(datekey) from DWRepository..DateDim;
20501230

Salim Assaf 6 Reputation points

2021-01-01T09:12:21.053+00:00

As long as you have Dec 31, 2020 in your datedim tables you are fine and I wouldn't worry about the duplicate key error. You can check by running a command like this to see if the entry now exists.

select * from DWRepository..DateDim where DateKey = 20201231

As to the UNION ALL issue, I have already fixed my system and found that if you list the table constraints you can usually figure out what is wrong. I used the query on the below website.

https://dataedo.com/kb/query/sql-server/list-all-table-constraints

I would replace the last line of the query with this as it will make it easier to view:
where table_view like '%ServiceRequestStatusDurationFact%' and constraint_type like '%Check%' order by details, constraint_type, table_view

Your constraints should be similar to what is shown in this attachment.
52656-sample.txt

mohammad saad 21 Reputation points

2021-01-01T09:43:17.643+00:00

Dear Salim,

below is the constraint and also table exist in date 20201231

dbo.ServiceRequestStatusDurationFact_2020_Dec Table Check constraint ServiceRequestStatusDurationFact_2020_Dec_Chk ([DateKey]>=(20201201) AND [DateKey]<=(20201230))
dbo.ServiceRequestStatusDurationFact_2021_Feb Table Check constraint ServiceRequestStatusDurationFact_2021_Feb_Chk ([DateKey]>=(20210201))

select * from DWRepository..DateDim where DateKey = 20201231

CalendarDate DateKey DayOfWeek DayNumberInMonth WeekNumberInYear CalendarMonth MonthNumber YearNumber CalendarQuarter FiscalQuarter FiscalMonth FiscalYear IsHoliday IsWeekDay IsLastDayOfMonth
2020-12-31 00:00:00 20201231 Thursday 31 53 December 12 2020 Q4 Q2 6 2021 0 1 1

mohammad saad 21 Reputation points

2021-01-01T09:51:46.597+00:00

Dear Salim,

As we have seen here that constraint of DEC datekey upper limit is [DateKey]<=(20201230))

should we run below command and then check services? please advice..

ALTER TABLE [dbo].[ServiceRequestStatusDurationFact_2020_Dec] DROP CONSTRAINT [ServiceRequestStatusDurationFact_2020_Dec_Chk];
ALTER TABLE [dbo].[ServiceRequestStatusDurationFact_2020_Dec] WITH CHECK ADD CONSTRAINT [ServiceRequestStatusDurationFact_2020_Dec_Chk] CHECK (([DateKey]>=(20201201) AND [DateKey]<=(20201231)));
ALTER TABLE [dbo].[ServiceRequestStatusDurationFact_2020_Dec] CHECK CONSTRAINT [ServiceRequestStatusDurationFact_2020_Dec_Chk];

Thanks

mohammad saad 21 Reputation points

2021-01-01T10:03:28.553+00:00

52753-dec-constaint.txt

please see list of dec constraint of all tables where upper limit is 30 dec,
Do we need to change all constraints upper limit to 31 manually?

Thanks

mohammad saad 21 Reputation points

2021-01-01T12:33:03.017+00:00

[52729-alter-constraint-for-dec-2020.txt][1]

mohammad saad 21 Reputation points

2021-01-01T12:33:27.81+00:00

I have executed alter-constraint-for-dec-2020.txt on DWRepository, DWDataMart and OMDWDataMart databases in the attached and add 31 date in all tables' constraint but still getting below errors..

Message: UNION ALL view 'DWDataMart.dbo.ServiceRequestSupportGroupStatusDurationFactvw' is not updatable because a partitioning column was not found
Message: UNION ALL view 'DWDataMart.dbo.EntityManagedTypeFactvw'
Message: UNION ALL view 'DWRepository.dbo.EntityManagedTypeFactvw'
Message: UNION ALL view 'DWDataMart.dbo.SLAInstanceInformationFactvw'
Message: UNION ALL view 'DWDataMart.dbo.EntityRelatesToEntityFactvw'
Message: UNION ALL view 'DWDataMart.dbo.ReleaseRecordStatusDurationFactvw'
Message: UNION ALL view 'DWDataMart.dbo.ActivityStatusDurationFactvw'
Message: UNION ALL view 'DWDataMart.dbo.ActivityStageDurationFactvw'

and more....

mohammad saad 21 Reputation points

2021-01-01T12:37:17.433+00:00

below are the dates in SP

@StartDay smalldatetime = '20000101', @EndDay smalldatetime = '20201231'

Thanks

Salim Assaf 6 Reputation points

2021-01-01T18:27:02.97+00:00

The query on the below website should help you identify any issues with your constraints. Do you notice anything else wrong with your constraints? Perhaps attach a file of the query output for one or more of those tables and it should give us an idea of what is wrong.

https://dataedo.com/kb/query/sql-server/list-all-table-constraints

If you attach the query, please ensure you add the below statement which should make it easy to read :)
"where table_view like '%ServiceRequestStatusDurationFact%' and constraint_type like '%Check%' order by details, constraint_type, table_view"

Depending on your issue, you can probably use one of JimFinke's scripts to fix them all.

mohammad saad 21 Reputation points

2021-01-02T10:36:09.053+00:00

Dear Salim,

Thanks for the query, so far I have done below but still no luck..

step1:
Exec DWRepository.dbo.PopulateDateDim '20201231','20501231'
Exec DwDataMart.dbo.PopulateDateDim '20201231','20501231'
Exec CMDwDataMart.dbo.PopulateDateDim '20201231','20501231'
Exec OMDwDataMart.dbo.PopulateDateDim '20201231','20501231'

mohammad saad 21 Reputation points

2021-01-02T10:36:23.15+00:00

step2
declare @objNameD sysname, @newChkD as nvarchar(max)
declare c cursor local FORWARD_ONLY READ_ONLY for
select name from sys.sysobjects where xtype='C' and name like '%_2020_Dec_Chk' escape '\' order by 1
open c; while 1=1 begin; fetch c into @objNameD if @@Fetch _STATUS<>0 break;
set @objNameD = replace(@objNameD,'_Chk','')
set @newChkD=replace('ALTER TABLE [dbo].[XYZFact_2021_Jan] DROP CONSTRAINT [XYZFact_2021_Jan_Chk];ALTER TABLE [dbo].[XYZFact_2021_Jan] WITH CHECK ADD CONSTRAINT [XYZFact_2021_Jan_Chk] CHECK (([DateKey]>=(20201201) AND [DateKey]<=(20201231)));ALTER TABLE [dbo].[XYZFact_2021_Jan] CHECK CONSTRAINT [XYZFact_2021_Jan_Chk];','XYZFact_2021_Jan',@objNameD)
print @newChkD
--exec sp_executesql @newChkD
end; close c; deallocate c;

mohammad saad 21 Reputation points

2021-01-02T10:38:18.087+00:00

after performing step1 and step2 on each DW databases got 31-dec date on each constraint as you can see in the attached as well but still no luck

52805-dw-constraint.txt

mohammad saad 21 Reputation points

2021-01-02T10:42:27.25+00:00

Dear Salim,

Constraint looks to me right as they are showing 31-DEC date,for instance see below there is an error on DW job but on Database it looks fine, or is there something i am missing?

ETL Module Execution failed:

Message: UNION ALL view 'DWRepository.dbo.EntityRelatesToEntityFactvw' is not updatable because a partitioning column was not found.

table_view object_type constraint_type constraint_name details
________________________________________ _______ _____________ _______________________________________ _____________________

dbo.EntityRelatesToEntityFact_2020_Dec Table Check constraint EntityRelatesToEntityFact_2020_Dec_Chk ([DateKey]>=(20201201) AND [DateKey]<=(20201231))

Salim Assaf 6 Reputation points

2021-01-02T21:02:31.787+00:00

looking at your dw-constraint file you need to fix the following.

These constraints should have both a begin and end date:
DWRepository.dbo.ServiceRequestStatusDurationFact_2020_Oct
DWRepository.dbo.ServiceRequestStatusDurationFact_2021_Jan

No constraint on the below tables, these need to be added:
DWDataMart.dbo.ServiceRequestStatusDurationFact_2021_Jan
dbo.EntityRelatesToEntityFact_2021_Jan
dbo.ChangeRequestStatusDurationFact_2021_Jan

Drop and re-add the proper constraints and you should be fine :)

mohammad saad 21 Reputation points

2021-01-03T03:58:25.62+00:00

Thank you so much Dear Salim for helping me to identify this issue.
Do you think manual adding of dates will cause issue for future months or they will create automatically?
I have been trying to run @Jim Finke script but getting below error

Incorrect syntax near 'anonymous'.
Msg 137, Level 15, State 2, Line 15
Must declare the scalar variable "@objName".
Msg 137, Level 15, State 2, Line 16
Must declare the scalar variable "@objName".
Msg 137, Level 15, State 2, Line 17
Must declare the scalar variable "@objName".
Msg 195, Level 15, State 7, Line 18
'anonymous' is not a recognized SET option.
Msg 137, Level 15, State 2, Line 19
Must declare the scalar variable "@objName".
Msg 128, Level 15, State 1, Line 20
The name "anonymous" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

mohammad saad 21 Reputation points

2021-01-03T07:42:52.007+00:00

Dear Salim.

You are right that we need to add upper and lower limit in jan_2021 constraint.

I have manually updated more than 50 tables' constraint in DWDataMart/DWrep/OMDWDataMart/CMDDWDatamart databases (script attached with constraints view as well) and those are now not in union error in DW job logs, but now new tables comes in, could you please help in providing dynamic query for updating limits , that could help us alot.

Your help much appreciated .

mohammad saad 21 Reputation points

2021-01-03T08:04:18.947+00:00

mohammad saad 21 Reputation points

2021-01-03T08:08:12.87+00:00

52866-new-dw-cons.txt

Wyatt Wong 116 Reputation points

2021-01-05T02:00:39.05+00:00

There is NO need to modify the Stored Procedure at all

Wyatt Wong 116 Reputation points

2021-01-05T02:03:04.077+00:00

If you follow the procedures from @D B , then you should found that there is NO need to manually update the SP and constraints by yourself. If you have manually modify the SP and constraints, I am NOT sure if it will introduce any new issues.

mohammad saad 21 Reputation points

2021-01-06T07:46:50.06+00:00

but i am not able to get begin and end date for jan-21 with the SP you have mentioned..
And all the error gone after manually added the jan-21 upper and lower limit

Thanks

C Sharp Conner 1 Reputation point

2021-01-13T13:56:09.583+00:00

My solution: https://learn.microsoft.com/en-us/answers/questions/228365/transformcommon-fail-error-4457-the-attempted-inse.html
Sign in to comment
Shumail Usmani 1 Reputation point

2021-01-01T08:25:58.16+00:00

After applying fix SalimAssaf-7299 getting below error

ETL Module Execution failed:
ETL process type: Load
Batch ID: 2225732
Module name: LoadDWDataMartServiceRequestStatusDurationFact
Message: UNION ALL view 'DWDataMart.dbo.ServiceRequestStatusDurationFactvw' is not updatable because a partitioning column was not found.

Stack: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SystemCenter.Warehouse.Utility.SqlBulkOperation.PartitionedViewUpdate(SqlConnection sourceConnection, String sourceQuery, String destinationTable, Dictionary2 mapping, SqlConnection destinationConnection, Collection1 pkColumns)
at Microsoft.SystemCenter.Warehouse.Utility.SqlBulkOperation.PartitionedViewUpsert(String sourceConnectionString, String sourceQuery, String destinationTable, Dictionary2 mapping, String destinationConnectionString, Collection1 pkColumns, Int32& insertCount, Int32& updateCount, DomainUser sourceSecureUser, DomainUser destSecureUser, SqlResourceStore targetStore)
at Microsoft.SystemCenter.Warehouse.Etl.ADOInterface.PartitionedViewUpsert(DomainUser sourceConnectionUser, DomainUser destinationConnectionUser)
at Microsoft.SystemCenter.Warehouse.Etl.ADOInterface.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser)
at Microsoft.SystemCenter.Warehouse.Etl.LoadModule.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser, Int32 loadBatchSize)
at Microsoft.SystemCenter.Warehouse.Etl.LoadModule.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser)
at Microsoft.SystemCenter.Etl.ETLModule.OnDataItem(DataItemBase dataItem, DataItemAcknowledgementCallback acknowledgedCallback, Object acknowledgedState, DataItemProcessingCompleteCallback completionCallback, Object completionState)
Please sign in to rate this answer.

0 comments No comments
Sign in to comment