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

Anton Tolmachev 11 Reputation points

2021-01-01T11:15:34.873+00:00

Hey guys, Happy NY !

After applying initial fix provided by @D B initially on Dec 04 -So far so good, jobs came back all seemed ok and quiet.

On 31.12.2020 started getting errors again (with ETL jobs failing) of that format :

----
An error countered while attempting to execute ETL Module:
ETL process type: Transform
Batch ID: 553957
Module name: TransformComputerHasSoftwareUpdateInstalledFact
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="TransformComputerHasSoftwareUpdateInstalledFactProc" LineNumber="220" Task="Step 9: Inserting into destination Fact"
....

----

Guess it was that initial fix missed DateDim on 31.12.2020. Had holiday streak started on 30th so was NOT able to catch up on that during that moment (didin't fix DateDims)

OK, NY started, errors mentioned above gone by themselves (again, did NOTHING for now still ) and now it's just same as @Shumail Usmani :

UNION ALL view <xxxx>vw is not updatable because a partitioning column was not found.

so we are back again from on what was started initially..duh.

Anyone has a working fix for that again for now?? Cause things started to be pretty ridiculous on that, the fix needs to be fixed,whatsoever...
Not a big SQL guy here, but maybe got a template of actions to get rid of this sh*t again??
Ok, i see folks above give way to figure out problem table constraints, what's next, a way to actually FIX them back again?

SCSM 1801 (2016)
Please sign in to rate this answer.
Wyatt Wong 116 Reputation points

2021-01-04T10:36:41.74+00:00

If you follow what @D B mentioned, you should NOT receive further errors as the constraints have been extended from 31-Dec-2020 to 31-Dec-2050.

mohammad saad 21 Reputation points

2021-01-04T17:50:23.66+00:00

Partition column not found error occurred on DW JOBs on 1st-dec-2020, after that @D B has provided the script which help us in this resolution.

no issue till 30-dec-20 But on 31-dec-2020 same error appeared again,and we have seen that dec-31 date missing which is also identified by @Salim Assaf
And provided script for updating date for 31-dec date in dim.table

And we update the constraint of 31-DEC-2020 date with the help of @Anonymous script
But after that error was still there on further checking with @Salim Assaf , he has pointed that we need to update JAN-2021 Constraint with begin and end date

although @Jim Finke has provided dynamic script for jan-2021 with upper and lower limit but script giving error so no choice but to manually create script for all dw databases tables' constraint

after updating all DW constraint manually , all errors are now gone :)
thanks to all for your help .
let see in feb all go right :)

Camilo Andres Vanegas Ramirez 11 Reputation points

2021-01-04T18:05:54.473+00:00

mohammadsaad-5988 se posible que nos compartas en el post el paso a paso de lo que hiciste para solucionar las fallas??, seria de gran ayuda si nos puedes compartir esto dado que veo que muchas personas tenemos fallas con este tema de SCSM sin documentación o ayuda por parte de @Microsoft

mohammad saad 21 Reputation points

2021-01-04T18:15:50.247+00:00

o problema não tem mais do que 1000 caracteres, como posso compartilhá-lo aqui?

Wyatt Wong 116 Reputation points

2021-01-05T03:53:54.393+00:00

Sorry I found that you are right and I just found that the same error happened again after 31-Dec-2020.

Anton Tolmachev 11 Reputation points

2021-01-05T19:30:24.803+00:00

Dear @mohammad saad , kindly share the script mentioned for this part :

"so no choice but to manually create script for all dw databases tables' constraint"

Wyatt Wong 116 Reputation points

2021-01-06T01:48:56.077+00:00

I referred to the posts from @Jim Finke and successfully resolved the failed issue of:

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

mohammad saad 21 Reputation points

2021-01-06T07:35:39.717+00:00

53777-omd-cmd-cons-jan-upd.txt

mohammad saad 21 Reputation points

2021-01-06T07:36:47.877+00:00

53778-dw-repo-jan-cons-upd.txt

mohammad saad 21 Reputation points

2021-01-06T07:37:30.39+00:00

53961-dwdatamart-jan-cons-upd.txt

mohammad saad 21 Reputation points

2021-01-06T07:40:17.543+00:00

@Camilo Andres Vanegas Ramirez
@Anton Tolmachev

I have attached script for each databases that i have run for resolving the issue.

Please follow the steps as mention by @Wyatt Wong and then finally run the scripts for jan begin and end date that i have attached.

Thanks

mohammad saad 21 Reputation points

2021-01-06T08:06:48.283+00:00

please follow what @Wyatt Wong suggested in the last then you don't need to do manual work.

Thanks,

Wyatt Wong 116 Reputation points

2021-01-07T02:43:18.73+00:00

I hope your issue is properly fixed after following my posts. Otherwise, please let us know any additional issues that you have encountered.

Daniel Luck 1 Reputation point

2021-01-10T20:02:24.05+00:00

Dear Sir,

I tried this script and it has errors about items not in the DB?

Thanks

Daniel

Eg:

Msg 4902, Level 16, State 1, Line 218
Cannot find the object "dbo.IncidentRelatesToActionLogFact_2021_Jan" because it does not exist or you do not have permissions.
Msg 4902, Level 16, State 1, Line 220
Cannot find the object "dbo.IncidentRelatesToActionLogFact_2021_Jan" because it does not exist or you do not have permissions.
Msg 4902, Level 16, State 1, Line 222
Cannot find the object "dbo.IncidentRelatesToActionLogFact_2021_Jan" because it does not exist or you do not have permissions.
Msg 4902, Level 16, State 1, Line 227
Cannot find the object "dbo.IncidentRelatesToAnalystCommentLogFact_2021_Jan" because it does not exist or you do not have permissions.

Daniel Luck 1 Reputation point

2021-01-10T20:03:16.87+00:00

Dear Sir,

When applying this script it states items are not there ?

Thanks

Daniel

Daniel Luck 1 Reputation point

2021-01-10T20:04:01.53+00:00

This one works ok.

Daniel

Daniel Luck 1 Reputation point

2021-01-10T20:06:34.523+00:00

This one didnt work on mine.

Please note that on my system your script havent fixed:

Transform.Common
Load.Common.

Your scripts have fixed the other ones.

Are you able to help?

Thanks

Daniel

mohammad saad 21 Reputation points

2021-01-11T07:03:37.177+00:00

I have updated script for you for DWDATAMART and DWREPOSITORY DATABASE.
As you have mentioned that two scripts run fine and you are getting errors on remaining two scripts, run new scripts and check.

Thanks
55125-jan-2021-constraint-updted-repo.txt

55217-dwdatamart-jan-2021-updated.txt

Wyatt Wong 116 Reputation points

2021-01-19T01:51:54.77+00:00

Have you successfully resolved the issues for Transform.Common and Load.Common ?
Sign in to comment
Wyatt Wong 116 Reputation points

2021-01-05T03:36:13.777+00:00

@D B I am now getting following errors on 31-Dec-2020 with the same error messages of "UNION ALL view 'xxxvw' is not updatable because a partitioning column was not found." How do I fix it agan?

Transform.Common Failed
Load.CMDWDataMart Failed
Load.Common Failed
Load.OMDWDataMart Failed
Please sign in to rate this answer.
Camilo Andres Vanegas Ramirez 11 Reputation points

2021-01-05T15:57:32.463+00:00

nosotros presentamos el mismo error, @microsoft no ha generado documentación o procedimiento para la solución de las fallas, si alguno sabe como solucionarlo, les agradecería la ayuda que nos puedan dar, buen dia

Wyatt Wong 116 Reputation points

2021-01-06T01:49:25.77+00:00

I have resolved the issues.

Dmat-2323 1 Reputation point

2021-01-06T04:06:30.687+00:00

What did you do to fix the issue?

Wyatt Wong 116 Reputation points

2021-01-06T07:12:58.387+00:00

Thanks for asking. First I notice @mohammad saad mentioned that he saw the 31-Dec-2020 was missing, then he mentioned @Salim Assaf . I referred to @Salim Assaf 's post and found that he mentioned we need to execute the following script to add the missing date of 31-Dec-2020.

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

Wyatt Wong 116 Reputation points

2021-01-06T07:15:47.483+00:00

@Salim Assaf further mentioned that we can check if the table contains the DateKey of 20201231 or not by executing the following query:

select * from DWRepository..DateDim where DateKey = 20201231

By similarity, I executed the following SQL statements as well:

select * from DwDataMart.dbo.PopulateDateDim where DateKey = 20201231
select * from CMDwDataMart.dbo.PopulateDateDim where DateKey = 20201231
select * from OMDwDataMart.dbo.PopulateDateDim where DateKey = 20201231

And in my case, I found that my DWRepository..DateDim table already have an entry of DateKey of 20201231

However, I got no rows returned when I executed the other 3 SQL queries.

Wyatt Wong 116 Reputation points

2021-01-06T07:17:22.357+00:00

So I only have to executed the following 3 SQL queries

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

Then I re-run the following select queries and found that all tables have the row with DateKey of 20201231

select * from DWRepository..DateDim where DateKey = 20201231
select * from DwDataMart.dbo.PopulateDateDim where DateKey = 20201231
select * from CMDwDataMart.dbo.PopulateDateDim where DateKey = 20201231
select * from OMDwDataMart.dbo.PopulateDateDim where DateKey = 20201231

Wyatt Wong 116 Reputation points

2021-01-06T07:38:49.773+00:00

Then I followed @Salim Assaf 's post and made use of the SQL statement from the following URL to list out all table constraints

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

And I noticed the Fact table for Dec-2020 have the constraint of "DateKey <= 20201230" instead of "DateKey <= 20201231", and the table for Jan-2021 is only single bounded by "DateKey <= 20210131" instead of double bounded by "DateKey >= 20210101 AND DateKey <= 20210131". I have provided the image of one of the fact tables below for your reference.

Wyatt Wong 116 Reputation points

2021-01-06T07:48:13.183+00:00

I followed the post from @Jim Finke and execute the following SQL Statement to fix the DateKey constraint for Dec-2020 and Jan-2020. Note that I have to execute the script for each of the 4 Databases in order to generate 4 different set of ALTER TABLE statements.

DWRepository
DWDatamart
CMDwDatamart
OMDwDatamart

Noticed that the script only print out the ALTER TABLE statements on screen, you need to copy the ALTER TABLE and execute it for EACH of the above 4 databases. After that, I used the script https://dataedo.com/kb/query/sql-server/list-all-table-constraints to double check the Dec-2020 and Jan-2021 tables have the correct constraints, then I restarted the SCSM services in SCSM Data Warehouse server and monitor the SCSM DW Jobs for Load.Common, Transform.Common, Load.CMDWDataMart and Load.OMDWDataMart can successfully run into completion.

mohammad saad 21 Reputation points

2021-01-06T07:49:50.237+00:00

hmm right, and what about FEB-2021? it is also single bounded by datekey..

Do you think we need to manually update FEB datekey for double bounded?

Thanks

mohammad saad 21 Reputation points

2021-01-06T08:03:17.63+00:00

oh alright!!!!

GOT IT!

all i needed that just change the date in the procedure and run on each database and then execute the output accordingly, ouch i did so much manual work :(

Thanks for identifying this!

Wyatt Wong 116 Reputation points

2021-01-06T09:31:35.87+00:00

As opposed to what @Anton Tolmachev mention, there is NO NEED for me to create script for all dw databases tables' constraint"manually.

Wyatt Wong 116 Reputation points

2021-01-06T09:43:03.997+00:00

@Camilo Andres Vanegas Ramirez You may refer to my posts above to see if it is relevant to you.

Wyatt Wong 116 Reputation points

2021-01-06T09:44:56.397+00:00

@Salim Assaf further mentioned that we can check if the table contains the DateKey of 20201231 or not by executing the following query:

select * from DWRepository..DateDim where DateKey = 20201231

By similarity, I executed the following SQL statements as well:

select * from DwDataMart.dbo.PopulateDateDim where DateKey = 20201231
select * from CMDwDataMart.dbo.PopulateDateDim where DateKey = 20201231
select * from OMDwDataMart.dbo.PopulateDateDim where DateKey = 20201231

And in my case, I found that my DWRepository..DateDim table already have an entry of DateKey of 20201231

However, I got no rows returned when I executed the other 3 SQL queries.

Wyatt Wong 116 Reputation points

2021-01-06T10:38:26.477+00:00

I have not tackle with the Feb-2021 fact table at this moment. We are migrating to SCSM 2019 sooner or later.

If the error of UNION ALL view '<xxxx>vw' is not updatable because a partitioning column was not found." still persists in the following 4 SCSM DW Jobs in Feb-2021 and we have not yet migrated to SCSM 2019, then I should know how to handle it.

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

mohammad saad 21 Reputation points

2021-01-06T10:56:31.717+00:00

hmm you are right! we need to set the begin/end date for feb as well if encounter same issue again other option is to upgrade only.

Thank you

mohammad saad 21 Reputation points

2021-01-06T12:10:31.617+00:00

--feb-2021
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_Feb')
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]>=(20210201) AND [DateKey]<=(20210228)));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-06T12:11:02.493+00:00

@Wyatt Wong I have edited script for feb that you shared, looks right?

Anton Tolmachev 11 Reputation points

2021-01-06T12:57:54.297+00:00

Ok, fixed mine, ETL Jobs proceeding, errors gone for now. Big Thanks to @Wyatt Wong for summarized steps, can be not an easy one - to follow through all the conversation from starters and catch important points.

as @mohammad saad mentioned, don't forget to Edit --Jan 2021 script's dates to actual JANUARY date range (if you're plain copying from @Jim Finke ) / check with @Wyatt Wong SCREENS
and then execute output (ALTER TABLE...) of EACH --Dec and -- Jan script on EACH of DB's listed on posts above.
scripts for cross checking dates on tables at DWRepository and DWDataMart DB's - as mentioned on above links (better edit last line of it as @Salim Assaf suggested for easier output)
Not sure how it'll go for Feb month then, the issue is really nasty in overall, hope if it comes - folks still can give a follow up, appreciated much.
Need to jump off to SCSM 2019 really, but still not there cause some inner organizational mess :(

Abdellah 6 Reputation points

2021-01-06T15:09:26.84+00:00

Hi everybody!

First of all, I appreciate all the efforts you are putting here to find a solution to this incident. Thank you and everyone here.

After applying the two scripts to correct the missing day "20201231" for December and for January 2021 using the above solution. I run the query provided by Mr. @Salim Assaf to identify if December 2020 and January 2021 are corrected which seems normal, but there is something wrong with the constraint "ServiceRequestStatusDurationFact_2020_Aug_Chk", it indicates the end date of October 2020!!

I guess this is why the DataWarehouse jobs always fail.

Could you please see the screenshots, first one the result of the constraint query and the second for an example of "Load.CMDWDataMart" job errors:

Thank you for your attention. Looking forward to your response.

Zakaria Muhammad 161 Reputation points

2021-01-06T15:42:27.26+00:00

Seems like for me their is no output after running the followings and my ETL is broken

select * from DWRepository..DateDim where DateKey = 20201231
select * from DwDataMart.dbo.DateDim where DateKey = 20201231

Camilo Andres Vanegas Ramirez 11 Reputation points

2021-01-06T15:45:40.4+00:00

hola buenos dias

quisiera saber que ejecuto primero en los pasos que realizo para solucionar el problema, porque no me queda claro, gracias.

se ejecuta primero este paso?

select * from DWRepository..DateDim where DateKey = 20201231

By similarity, I executed the following SQL statements as well:

select * from DwDataMart.dbo.PopulateDateDim where DateKey = 20201231
select * from CMDwDataMart.dbo.PopulateDateDim where DateKey = 20201231
select * from OMDwDataMart.dbo.PopulateDateDim where DateKey = 20201231

And in my case, I found that my DWRepository..DateDim table already have an entry of DateKey of 20201231

o primero ejecuto el paso indicado por @Jim Finke

mil gracias, quedo a la espera de la respuesta con el fin de proceder, gracias.

Anton Tolmachev 11 Reputation points

2021-01-06T17:12:29.48+00:00

the mentioned query should be actually :

select * from DWRepository..DateDim where DateKey = 20201231
select * from DWDataMart.dbo.DateDim where DateKey = 20201231
select * from CMDwDataMart.dbo.DateDim where DateKey = 20201231
select * from OMDwDataMart.dbo.DateDim where DateKey = 20201231

i guess the text it's the text formatting broke it or something.

Anton Tolmachev 11 Reputation points

2021-01-06T17:15:56.64+00:00

yup. the formatting removes asterisk from the text message on the forum.

see screen attached as it should be.

Wyatt Wong 116 Reputation points

2021-01-07T02:36:51.267+00:00

I would be greatly appreciated if you post in English.

Wyatt Wong 116 Reputation points

2021-01-07T02:39:38.77+00:00

@Zakaria Muhammad I have already updated the post by adding a backslash character in front of the asterisk character. Now the asterisk character can be properly displayed.

Wyatt Wong 116 Reputation points

2021-01-07T03:09:54.627+00:00

The screen shots are too small to see. Could you enlarge the fonts and then paste (Ctrl-V) the image to this forum again ?

Can you also run poweshell "Get-SCDWJob" in SCSM DW Management Server and post the output here?

Abdellah 6 Reputation points

2021-01-07T08:22:56.223+00:00

Ok Mr. WyattWong-6731 here is the screen shots:

for the output of the command above I get this error :
(I didn't found the Service Manager Shell on SCSM DW Management Server so I ran the command on Service Manager Server's Power Shell).

Wyatt Wong 116 Reputation points

2021-01-07T10:56:58.233+00:00

According to your output, the problem happened in the CMDWDataMart database.

To run the "Get-SCDWJob" command, please remote connect to your SCSM Data Warehouse Management Server (NOT the Service Manager Management Server), then open a PowerShell prompt and execute the following commands toload the cmdlets:

====================
$props = Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\System Center\2010\Common\Setup"
$instdir = $props.InstallDirectory
$dwpsd = $instdir + "Microsoft.EnterpriseManagement.Warehouse.Cmdlets.psd1"

Import-Module -Name $dwpsd

After that, you should be able to run "Get-SCDWJob"

Wyatt Wong 116 Reputation points

2021-01-07T10:58:23.867+00:00

As someone mentioned it maybe correct that the constraints in Feb-2021 is single-bounded, so I will NOT try to update the constraints for the Feb-2021 fact tables at this moment.

Wyatt Wong 116 Reputation points

2021-01-07T11:11:36.8+00:00

Here is the output for my ServiceRequestStatusDurationFact_2020_Aug table, note that this fact table is stored in the DWDataMart Database.

Wyatt Wong 116 Reputation points

2021-01-07T11:16:54.043+00:00

So it looks like your problems happened in both CMDWDataMart and DWDataMart Databases.

Abdellah 6 Reputation points

2021-01-07T14:43:08.107+00:00

Thank you Mr. WyattWong for the commands, "Get-SCDWJob" is working now, here is the screen shot of the SCSM DW jobs :

Wyatt Wong 116 Reputation points

2021-01-08T01:00:52.697+00:00

Please try to run this script created by myself:

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
w.StatusId = 2
ORDER BY StatusId DESC, p.ProcessName ASC

Wyatt Wong 116 Reputation points

2021-01-08T01:03:28.9+00:00

When you found there are rows with StatusId = 2, then post an image to show which SCSM DW Jobs have the failed Jobs.

Based on your Get-SCDWJob result, I guessed you will find failed rows from the Load.Common, Transform.Common, Load.CMDWDataMart and Load.OMDWDataMart databases.

Wyatt Wong 116 Reputation points

2021-01-08T05:44:58.657+00:00

The forum did not allow me to submit the post with the SQL statement, you can refer to the previous script and modify it so that the DateKey for Aug-2020 was changed to

([DateKey] >= (20200801) AND [DateKey] <= (20200831))

Then you can select all the "ALTER TABLE" statements and execute in the [DWDataMart] database.

You can check if there are similar errors from other 3 databases, if so you can apply the same logic to fix the DateKey constraint properly.

Wyatt Wong 116 Reputation points

2021-01-08T05:45:54.037+00:00

The forum did not allow me to submit the post with the SQL statement, you can refer to the previous script and modify it so that the DateKey for Aug-2020 was changed to

([DateKey] >= (20200801) AND [DateKey] <= (20200831))

Then you can select all the "ALTER TABLE" statements and execute in the [DWDataMart] database.

You can check if there are similar errors from other 3 databases, if so you can apply the same logic to fix the DateKey constraint properly.

Wyatt Wong 116 Reputation points

2021-01-08T06:48:08.013+00:00

Congratulations.

Abdellah 6 Reputation points

2021-01-08T09:12:01.627+00:00

I changed the DateKey for Aug-2020 to ([DateKey] >= (20200801) AND [DateKey] <= (20200831)) the screenshot is below :

I captured in my first post an example of some errors on "JOB-LoadCMDWDataMart", here are the screenshots of all errors on DW JOBS :

Error_JOB-LoadCMDWDataMart_DB-CMDWDataMart :

Error_JOB-LoadCommon_DB-DWDataMart :

Error_JOB-LoadOMDWDataMart_DB-OMDWDataMart :

Error_JOB-Transform.Common_DB-Repository :

Abdellah 6 Reputation points

2021-01-08T09:47:08.547+00:00

I changed the August 2020 DateKey constraint from: [DateKey] <= 20201031 to ([DateKey]> = (20200801) AND [DateKey] <= (20200831))

using the script below and after applying the results on the 4 DBs :

Some tables did not update the constraint DateKey, it outputs the messege below :

And after checking those tables they don't have any constraint (Exemple: "ComputerHostsLogicalDiskFact_2020_Aug" Table) :

Wyatt Wong 116 Reputation points

2021-01-11T08:27:37.523+00:00

After executing the script that you generate from the small store procedure above, you should be able to add the constraint to the dbo.ComputerHostsLogicalDiskFact_2020_Aug table. I checked that a constraint exists in my dbo.ComputerHostsLogicalDiskFact_2020_Aug table

Wyatt Wong 116 Reputation points

2021-01-19T01:55:20.153+00:00

I supposed you have fix all the issues to the following 4 SCSM DW Jobs.

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

Wyatt Wong 116 Reputation points

2021-01-22T07:46:44.18+00:00

@Andrew Gordon Can you run the above script written by me and show me the output which tables have status = 2 ?
Sign in to comment
Anj277 1 Reputation point

2021-01-07T12:58:14.387+00:00

We faced the same issue and applied the fix as suggested in this thread.
But the jobs are still failing with errors like "UNION ALL view 'OMDWDataMart.dbo.DeployedComputerRunsWindowsComputerFactvw' is not updatable because a partitioning column was not found", "UNION ALL view 'OMDWDataMart.dbo.DeployedComputerRunsWindowsComputerFactvw' is not updatable because a partitioning column was not found"

Could someone please help
Please sign in to rate this answer.
Salim Assaf 6 Reputation points

2021-01-07T15:21:13.76+00:00

This was mentioned earlier but you can run a query to list the table constraints and that should give you an idea of 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 something like this to put them all in order
where table_view like '%DeployedComputerRunsWindowsComputerFact%' and constraint_type like '%Check%' order by details, constraint_type, table_view

Your constraints should be similar to what is shown in this attachment. As you can see every month should have a constraint, and all the tables excluding the first and last should have a lower and upper range value. You can drop/recreate the constraints to fix any issues or use one of the many fixed listed in this thread to fix the constraints once you identify the month(s) that are giving you problem.
54380-sample.txt

Dmat-2323 1 Reputation point

2021-01-07T15:26:43.873+00:00

I am having the same issue, my constraints match your Sample.txt with the exception of I dont have Feb 2021 at all. I wouldn't think that would be the cause.

Salim Assaf 6 Reputation points

2021-01-07T15:27:56.307+00:00

Please attach your query results

Dmat-2323 1 Reputation point

2021-01-07T16:05:24.507+00:00

I am getting an error with the DWRepository, DWRepository.dbo.changeRequeststatusDurationFactvw is not updateable because of partitioning column was not found. When I run the script against the DWRepository I get . If I run it against DWDataMart I get this:, Ending at Jan 2021.

David Ulrich 1 Reputation point

2021-01-07T16:15:10.817+00:00

on DW Repository -

Anj277 1 Reputation point

2021-01-07T16:58:14.663+00:00

My results are also similar to this.
In DW repository starting from Oct 2020 to Jan 2021 only.

Anj277 1 Reputation point

2021-01-07T17:02:04.953+00:00

Please find attached results after running the query.54330-output-dwrepository.txt 54418-output-owdatamart.txt

Salim Assaf 6 Reputation points

2021-01-07T17:41:38.767+00:00

I agree, everything looks great except for your Feb constraints. If the tables exist in your system, and I'm certain they you do you would need to add the constraints. If you look through this thread, there should be a script (or script you can modify) to make the changes.

David Ulrich 1 Reputation point

2021-01-07T17:59:35.8+00:00

looks like this .. obviously missing ..

Anj277 1 Reputation point

2021-01-07T18:59:30.853+00:00

Thank You. Appreciate your support.
Added feb constraints and the issue is solved.

Dmat-2323 1 Reputation point

2021-01-07T19:04:23.767+00:00

Do you think I just need the start constraints like yours? I don't see a script to create the starting constraints.

Dmat-2323 1 Reputation point

2021-01-07T19:05:26.017+00:00

Can you please share the script you ran to generate Feb constraints?

Anj277 1 Reputation point

2021-01-07T19:28:08.487+00:00

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_Feb')
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]>=(20210201) AND [DateKey]<=(20210228)));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;

Wyatt Wong 116 Reputation points

2021-01-11T02:43:34.873+00:00

I would suggest NOT to mess up any constraints for Feb-2021 as we didn't know what the outcome will do. I would rather focus on solving the issues for Jan-2021.

Dmat-2323 1 Reputation point

2021-01-11T14:05:44.337+00:00

Everything is now running except my transform I am getting an error saying "The INSERT statement conflicted with the FOREIGN Key constraint "FK_Computerhassoftwareupdateinstalledfact_2020_Dec_datekey_DateDim" The conflict occurred in the database "DWRepository", table dbo-DateDim, column DateKey. Any suggestions?
Sign in to comment
David Ulrich 1 Reputation point

2021-01-07T15:01:09.14+00:00

Hi same issue as above here .. same errors same jobs
Please sign in to rate this answer.

0 comments No comments
Sign in to comment