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

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.

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.

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.

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.

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.

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

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