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 ·
DB-7386 avatar image
6 Votes"
DB-7386 answered WyattWong-6731 commented

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:

  1. Backup all DW DBs

  2. 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'

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

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

· 25
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.

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

1 Vote 1 ·

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

Thank you!

1 Vote 1 ·

This just worked for us! Thank you!!

1 Vote 1 ·

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

0 Votes 0 ·

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

0 Votes 0 ·

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


0 Votes 0 ·
Show more comments

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:

45972-output.png

0 Votes 0 ·
output.png (21.1 KiB)

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.

0 Votes 0 ·
Show more comments
Show more comments
AndrewGordon-8681 avatar image
0 Votes"
AndrewGordon-8681 answered WyattWong-6731 commented

The same thing has just started happening to me two days ago for no apparent reason. I've tried restoring the databases from backup, but that isn't working either. I hope that somebody will have a solution 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.

What version of SCSM are you using ?

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

2012 SP1.
I'm following the steps found here, https://gotoguy.blog/2013/12/13/data-warehouse-jobs-fails-when-upgrading-service-manager-2012-from-sp1-to-r2/, and am trying the fix of creating new PKs for each table that is showing an error. I hope this fixes it. If it does, I'll let you know here.

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

I believed the given URL describes another issue related to missing primary key. Furthermore, I am not performing SCSM 2012 SP1 upgrade to SCSM 2012 R2. The problem happens for no specific reason as we have been running SCSM 2012 SP1 since 2011 and the mentioned issue just happened on 01-Dec-2020 around 08:00am

0 Votes 0 ·

Ok, interesting. My issue started about the same time as well. Coincidence? I did recreate all the primary keys (PKs) for all the entries that were showing as having an error associated with Load entries. I then restarted the jobs and low and behold, all the PKs disappeared again. There went two hours of my life. I'm leaving the Load and Transform disabled and letting the rest of the ETL jobs and Cubes run for now until I think of something else to try tomorrow as it is almost quitting time for today here in NZ. Maybe my 3 AM aha moment will shed some clarity and light upon the situation.

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

Hi
On our own scsm system (SCSM 2016) the same problem and approx. it began at the same time. One of our clients (SCSM 2012 R2) also experienced the same situation, also from 01.12.2015.

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

It looks like a known issue or a bug in SCSM

0 Votes 0 ·
NorbertPelenczei-2637 avatar image
0 Votes"
NorbertPelenczei-2637 answered

There may be a reason for the error: all ... Fact_2021_Jan table is missing Constraints, probably after creating it, the problem will be solved. I will test and give feedback tomorrow.

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
0 Votes"
MonikStane-0596 answered camilovanegas commented

SCSM 2016 used for MIM 2016 reporting

I have the same problem since 01-Dec-2020

No constraint in Fact_2021_Jan table and very strange constraint in Fact_2020_Dec table - [DateKey]>=(20201201) AND [DateKey]<=(20201230) - where is 31. 12. 2020?



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

yes, that is strange now we are getting error today i,e 12/31/2020, everything was working fine till yesterday

0 Votes 0 ·

actualmente también presento las mismas fallas con SCSM, después de correr el procedimiento descrito en la parte superior funciono y todo continuo funcionando, sin embargo desde las horas de la noche del 31 de diciembre nuevamente la ejecución de los JOB's esta fallando, agradezco si alguien sabe como poder superar los problemas, gracias.

0 Votes 0 ·
DB-7386 avatar image
0 Votes"
DB-7386 answered WyattWong-6731 edited

same exact problem, started December 1st, SCSM 2012R2. we applied Windows patches on 11/28, but DW jobs ran for a few days after that, so probably not related...

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

Have opened a Microsoft support case, will update with any causes/resolutions here...

0 Votes 0 ·

Waiting for your further update on the resolution methods.

0 Votes 0 ·
ZakariaMuhammad-8648 avatar image
0 Votes"
ZakariaMuhammad-8648 answered ZakariaMuhammad-8648 published

Did anyone found the solution of this? for me ETL stop working same as Dec 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.

ZakariaMuhammad-8648 avatar image
0 Votes"
ZakariaMuhammad-8648 answered ZakariaMuhammad-8648 edited

Could be related to the 2020 as a leap year? I see the constraints on each fact month table the DEC entry is it should be 20201231

USE [DWRepository]
GO

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

ALTER TABLE [dbo].[EntityRelatesToEntityFact_2020_Dec] CHECK CONSTRAINT [EntityRelatesToEntityFact_2020_Dec_Chk]
GO


Any thoughts?


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

Interesting thought, but I didn't have and issue in 2016. I'm thinking an November patch is the culprit.

0 Votes 0 ·

We applied patches on November 20, 2020 and never had issues. The data in DWDataMart for reporting is till Nov 30th and no data from Dec 01 onward. this means the issue is with Dec Fact tables not with patches.

if it was patches it will break Data warehouse ETL immediately since they run all the time. We Deployed on Sep and October patches in our Production systems so November patches is not an issue. Also if we see the _2021_Jan fact table does not have Constraints in its table. don't know its by design.


44914-2020-12-03-13-37-38.jpg


0 Votes 0 ·

Ok, fair call. I can see the Dec Fact in our tables that are having issues. What I can't see is the PK for vx for each of these tables.

0 Votes 0 ·
AndreasBaumgarten avatar image
0 Votes"
AndreasBaumgarten answered WyattWong-6731 commented

Hi @XinGuo-MSFT
Maybe you can take a look on these SCSM Data Warehouse issues?



Kind regards
Andreas Baumgarten

· 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 spent about two hours recreating all the missing entries in the SQL databases, but after restarting all the ETLs afterwards, all of that work disappeared and all the same errors came back.
Oh how I love System Center!

0 Votes 0 ·

Oh, well.. I don't want to mess up the SCSM DW Database until I am sure what is causing the issue.

0 Votes 0 ·

Do you have any insights?

0 Votes 0 ·