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

D B 111 Reputation points

2020-12-03T16:31:59.393+00:00

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...
Please sign in to rate this answer.
D B 111 Reputation points

2020-12-03T17:55:13.307+00:00

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

Wyatt Wong 116 Reputation points

2020-12-04T02:18:55.977+00:00

Waiting for your further update on the resolution methods.
Sign in to comment
Zakaria Muhammad 161 Reputation points

2020-12-03T17:56:12.69+00:00

Did anyone found the solution of this? for me ETL stop working same as Dec 1.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
Zakaria Muhammad 161 Reputation points

2020-12-03T18:19:49.72+00:00

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?
Please sign in to rate this answer.
Andrew Gordon 11 Reputation points

2020-12-03T19:17:36.87+00:00

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

Zakaria Muhammad 161 Reputation points

2020-12-03T19:35:27.14+00:00

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.

Andrew Gordon 11 Reputation points

2020-12-03T19:39:20.02+00:00

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.
Sign in to comment
Andreas Baumgarten 96,601 Reputation points MVP

2020-12-03T18:34:36.04+00:00

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

----------

Kind regards
Andreas Baumgarten
Please sign in to rate this answer.
Andrew Gordon 11 Reputation points

2020-12-03T19:00:14.537+00:00

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!

Wyatt Wong 116 Reputation points

2020-12-04T02:20:52.4+00:00

Do you have any insights?

Wyatt Wong 116 Reputation points

2020-12-04T02:21:50.497+00:00

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