question

WyattWong-6731 avatar image
0 Votes"
WyattWong-6731 asked BillCrum-1926 commented

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

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

msc-service-manager
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

we are facing the same issue with SCSM 2012 R2 CU9

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

2 Votes 2 ·

I am also having the same issue with SCSM2016 1807

1 Vote 1 ·
PabloSchlegel-6243 avatar image
0 Votes"
PabloSchlegel-6243 answered

I have the same problem since 01-Dec-2020 (SCSM 2016)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AndrewGordon-8681 avatar image
0 Votes"
AndrewGordon-8681 answered

This is a list of my Facts that are showing errors. Does anybody else see the same ones?

LoadCMDWDataMart02ComputerHasSoftwareUpdateInstalledFact
LoadCMDWDataMart02ComputerHostsLogicalDiskFact
LoadCMDWDataMart02ComputerHostsOperatingSystemFact
LoadCMDWDataMart02ComputerHostsProcessorFact
LoadCMDWDataMart02ConfigurationManagerCollectionHasComputer
LoadCMDWDataMart02DeployedComputerRunsWindowsComputerFact
LoadCMDWDataMart02GroupContainsConfigItemFact
LoadCMDWDataMart02PowerActivityDayFact
LoadCMDWDataMart02ServiceContainsConfigItemFact
LoadDWDataMart02ActivityStageDurationFact
LoadDWDataMart02ActivityStatusDurationFact
LoadDWDataMart02ChangeRequestStatusDurationFact
LoadDWDataMart02EntityManagedTypeFact
LoadDWDataMart02EntityRelatesToEntityFact
LoadDWDataMart02IncidentStatusDurationFact
LoadDWDataMart02IncidentTierQueueDurationFact
LoadDWDataMart02ProblemStatusDurationFact
LoadDWDataMart02ReleaseRecordStatusDurationFact
LoadDWDataMart02ReviewerDecisionDurationFact
LoadDWDataMart02ServiceRequestStatusDurationFact
LoadDWDataMart02ServiceRequestSupportGroupStatusDurationFact
LoadDWDataMart02SLAInstanceInformationFact
LoadOMDWDataMart02ComputerHostsLogicalDiskFact
LoadOMDWDataMart02ComputerHostsOperatingSystemFact
LoadOMDWDataMart02ComputerHostsProcessorFact
LoadOMDWDataMart02ConfigurationManagerCollectionHasComputer
LoadOMDWDataMart02DeployedComputerRunsWindowsComputerFact
LoadOMDWDataMart02GroupContainsConfigItemFact
LoadOMDWDataMart02ServiceContainsConfigItemFact
TransformEntityManagedTypeFact
TransformEntityRelatesToEntityFact
TransformIncidentStatusDurationFact
TransformIncidentTierQueueDurationFact
TransformSLAInstanceInformationFact

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DB-7386 avatar image
0 Votes"
DB-7386 answered WyattWong-6731 commented

The official answer from Microsoft is to upgrade to SCSM 2016 UR 5 or newer...any versions prior to that were not developed to support dates into 2021 apparently.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for this information and feck! Has anybody gone for 2012 to 2016?

0 Votes 0 ·

that's really not a good response from MS. its not easy to upgrade the system with all the old tickets, process etc and Service Manager 2012 R2 still under extended support till 7/12/2022

1 Vote 1 ·

I totally agreed with ZakariaMuhammad-8648. Anyway, we are migrating our Windows Server OS from 2012 R2 to 2019. Due to the complexity of the upgrade from SCSM 2012 SP1 to SCSM 2019, we are planning to perform a fresh install SCSM 2019 without migrating the old tickets from existing SCSM 2012 SP1.

0 Votes 0 ·
DB-7386 avatar image
1 Vote"
DB-7386 answered AndrewGordon-8681 commented

I'm cleaning up my notes and will provide the sql queries to extend dates and add missing constraints...soon...

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GeorgesMouawad-12 avatar image
0 Votes"
GeorgesMouawad-12 answered

guys, is any of you facing problem on the management packs aswell? I have a set of reporting management packs that need to be re-deployed because they have a Failed Status, are you facing the same alongside the Transform Load jobs' Failures?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MonikStane-0596 avatar image
1 Vote"
MonikStane-0596 answered MonikStane-0596 edited

I think, MS Support solution forgot to change upper limit for *_2020_Dec_Chk from 20201230 -> 20201231

!! USE IT ON YOUR OWN RISK !!

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;

!! USE IT ON YOUR OWN RISK !!

To do real changes you must uncomment "--exec sp_executesql @newChkD" and comment "print @newChkD" and run it on DWRepository, DWDatamart, CMDwDatamart, OMDwDatamart

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Nice script, I didn't test it but the logic looks right. I didn't script this, I just used a similar search and used Excel to create the proper format.

I agree the original post is missing the end date and probably should be updated.

0 Votes 0 ·
ZakariaMuhammad-8648 avatar image
0 Votes"
ZakariaMuhammad-8648 answered mohammadsaad-5988 commented

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'**
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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)))

1 Vote 1 ·

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"

0 Votes 0 ·

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

20201230

0 Votes 0 ·
JimFinke-6249 avatar image
0 Votes"
JimFinke-6249 answered mohammadsaad-5988 commented

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 @WyattWong-6731 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.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

@DB-7386 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

0 Votes 0 ·

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".

0 Votes 0 ·

--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;

1 Vote 1 ·

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

0 Votes 0 ·
WyattWong-6731 avatar image
1 Vote"
WyattWong-6731 answered

I have written a simple powershell script (rename resetfailedscdwjob.txt to resetfailedscdwjob.ps1) to reset the failed SCSM Data Warehouse jobs and then send e-mail notification to supervisor and the support staff. I have created a Windows scheduled task to execute the powershell script every 15 minutes.

Alternatively, it is possible to reset a specific failed SCSM Data Warehouse job as a command line argument. For example, to reset the failed Process.SystemCenterWorkItemsCube, type the following command:

.\ResetFailedSCDWJob.ps1 Process.SystemCenterWorkItemsCube

Feel free to modify it to suit for your own needs.

[1]: /answers/storage/attachments/46874-resetfailedscdwjob.txt

[2]: /answers/storage/attachments/46921-emaillist.xml


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ShumailKhalid-0693 avatar image
0 Votes"
ShumailKhalid-0693 answered CSharpConner-9289 commented

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, Action`1 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, TaskCompletionSource`1 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

· 23
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·

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

0 Votes 0 ·

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


1 Vote 1 ·
sample.txt (6.2 KiB)
Show more comments