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 ·
ShumailUsmani-5110 avatar image
0 Votes"
ShumailUsmani-5110 answered

After applying fix SalimAssaf-7299 getting below error


ETL Module Execution failed:
ETL process type: Load
Batch ID: 2225732
Module name: LoadDWDataMartServiceRequestStatusDurationFact
Message: UNION ALL view 'DWDataMart.dbo.ServiceRequestStatusDurationFactvw' is not updatable because a partitioning column was not found.

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.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SystemCenter.Warehouse.Utility.SqlBulkOperation.PartitionedViewUpdate(SqlConnection sourceConnection, String sourceQuery, String destinationTable, Dictionary`2 mapping, SqlConnection destinationConnection, Collection`1 pkColumns)
at Microsoft.SystemCenter.Warehouse.Utility.SqlBulkOperation.PartitionedViewUpsert(String sourceConnectionString, String sourceQuery, String destinationTable, Dictionary`2 mapping, String destinationConnectionString, Collection`1 pkColumns, Int32& insertCount, Int32& updateCount, DomainUser sourceSecureUser, DomainUser destSecureUser, SqlResourceStore targetStore)
at Microsoft.SystemCenter.Warehouse.Etl.ADOInterface.PartitionedViewUpsert(DomainUser sourceConnectionUser, DomainUser destinationConnectionUser)
at Microsoft.SystemCenter.Warehouse.Etl.ADOInterface.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser)
at Microsoft.SystemCenter.Warehouse.Etl.LoadModule.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser, Int32 loadBatchSize)
at Microsoft.SystemCenter.Warehouse.Etl.LoadModule.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser)
at Microsoft.SystemCenter.Etl.ETLModule.OnDataItem(DataItemBase dataItem, DataItemAcknowledgementCallback acknowledgedCallback, Object acknowledgedState, DataItemProcessingCompleteCallback completionCallback, Object completionState)

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.

AntonTolmachev-1615 avatar image
0 Votes"
AntonTolmachev-1615 answered WyattWong-6731 commented

Hey guys, Happy NY !

After applying initial fix provided by @DB-7386 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 @ShumailUsmani-5110 :

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)


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

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

0 Votes 0 ·

Partition column not found error occurred on DW JOBs on 1st-dec-2020, after that @DB-7386 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 @SalimAssaf-7299
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 @MonikStane-0596 script
But after that error was still there on further checking with @SalimAssaf-7299 , he has pointed that we need to update JAN-2021 Constraint with begin and end date

although @JimFinke-6249 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 :)

0 Votes 0 ·

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

1 Vote 1 ·
Show more comments

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

0 Votes 0 ·

Dear @mohammadsaad-5988 , kindly share the script mentioned for this part :

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





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

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

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

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

0 Votes 0 ·

I have resolved the issues.

0 Votes 0 ·

What did you do to fix the issue?

0 Votes 0 ·
Show more comments

@camilovanegas You may refer to my posts above to see if it is relevant to you.

0 Votes 0 ·

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 @JimFinke-6249

54044-image.png

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


1 Vote 1 ·
image.png (182.1 KiB)
Show more comments

@SalimAssaf-7299 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

53887-image.png

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


0 Votes 0 ·

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

0 Votes 0 ·
Show more comments
Anj277-3810 avatar image
0 Votes"
Anj277-3810 answered Dmat2323-8659 commented

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 &apos;OMDWDataMart.dbo.DeployedComputerRunsWindowsComputerFactvw&apos; is not updatable because a partitioning column was not found", "UNION ALL view &apos;OMDWDataMart.dbo.DeployedComputerRunsWindowsComputerFactvw&apos; is not updatable because a partitioning column was not found"

Could someone please help

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


0 Votes 0 ·
sample.txt (6.2 KiB)

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.

0 Votes 0 ·

Please attach your query results

0 Votes 0 ·
Show more comments

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


0 Votes 0 ·
DavidUlrich-6924 avatar image
0 Votes"
DavidUlrich-6924 answered

Hi same issue as above here .. same errors same jobs

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.

DujonWalsham-0374 avatar image
0 Votes"
DujonWalsham-0374 answered WyattWong-6731 commented

I had this exact same issue, I was using SCSM 1801 when this happened.
Tried all of the fixes advised on this post but it didnt work.
Looks to be a bigger issue possibly at hitting a limit of how far the dates will go. I built an entirely new SCSM 1801 with fresh databases and the same issues still happened. Fixes never worked for that either.

The only definitive fix I found was to upgrade to SCSM 2019 and it worked perfectly.

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

This can be fixed, Microsoft have set a date limit and just needs to be changed.

I cannot understand why this limit was added though of December 2020 even for SCSM 2016?

Installing UR5 or above would have fixed it, or so they say. I am on RTM version.

I installed UR5 and it didn't fix it, but then again I did run the original fix on this forum.

The original fix didn't include 31/12/20 which has made our system go wrong again end of December.

My suggestion to anyone on this forum do not run the fixes here at the moment until someone can categorically say they fix it long term. I would also suggest to anyone who has run any fixes on here to speak to Microsoft and log a call with them which will cost money.

Currently working with Microsoft to fix this. They need to run sql scripts now to completely redo it.

0 Votes 0 ·

I agreed with you that they even imposed this restrictions to SCSM 2016.

0 Votes 0 ·
Dmat2323-8659 avatar image
0 Votes"
Dmat2323-8659 answered

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?

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.

DanielLuck-3255 avatar image
0 Votes"
DanielLuck-3255 answered DanielLuck-3255 edited

Dear All,

After speaking to Microsoft, it cost small amount and its fixed. If you get no where with this, I'd try this.

They have applied a temporary fix to get it working and they said I needed to update to at least UR5 for SCSM 2016.

I have updated to UR10 which is the latest update, and its all working now.

Kind Regards

Daniel

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 WyattWong-6731 commented

Good day everybody,
I've got all my dates correct, all my settings/Facts look fine for all my databases, but I have 37 Facts that are failing (see list 55523-etljobs.txt) and thus my Load and Transform jobs are failing. All have a similar error to this:

<Error EventTime="2021-01-11T19:42:07.1564642Z">UNION ALL view 'DWDataMart02.dbo.EntityManagedTypeFactvw' is not updatable because a partitioning column was not found.</Error>

Any thoughts about how I fix these?
Thank you.


etljobs.txt (3.1 KiB)
· 6
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 have posted the solution already. Could you refer to my previous threads in this post?

0 Votes 0 ·

Ok, must have missed that.

Would that also correct this issue?
dbo.ActivityStageDurationFact_2018_Jan Table Check constraint ActivityStageDurationFact_2018_Jan_Chk ([DateKey]<=(20180131))
I've tried to modify the script to fix previous missing DateKey, but it doesn't work.

Here is the output from my modified script.
ALTER TABLE [dbo].[ActivityStageDurationFact_2018_Jan] DROP CONSTRAINT [ActivityStageDurationFact_2018_Jan_Chk];
ALTER TABLE [dbo].[ActivityStageDurationFact_2018_Jan] WITH CHECK ADD CONSTRAINT [ActivityStageDurationFact_2018_Jan_Chk] CHECK (([DateKey]>=(20180101) AND [DateKey]<=(20180131)));ALTER TABLE [dbo].[ActivityStageDurationFact_2018_Jan] CHECK CONSTRAINT [ActivityStageDurationFact_2018_Jan_Chk];

0 Votes 0 ·

Do you still have the dbo.ActivityStageDurationFact_2018_Jan table ? I supposed last time you mentioned the table for Aug 2020.

0 Votes 0 ·

Please run the script in the following URL:

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

You need to run the script on all 4 databases below:

[DWRepository]
[DWDatamart]
[CMDwDatamart]
[OMDwDatamart]

0 Votes 0 ·
Show more comments

Sorry I did not know which of your Fact table have problems.

0 Votes 0 ·
GromovVyacheslav-3994 avatar image
0 Votes"
GromovVyacheslav-3994 answered WyattWong-6731 commented

Please help. I completed all recomendations but no result. I have errors for some modules
Module name: TransformEntityManagedTypeFact
Message: UNION ALL view 'DWRepository.dbo.EntityManagedTypeFactvw' is not updatable because a partitioning column was not found.

55721-1.png55625-2.png55675-3.png



1.png (60.5 KiB)
2.png (95.7 KiB)
3.png (69.7 KiB)
· 28
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.

If you follow what I posted, you should be able to resolve the issue.

0 Votes 0 ·

Please run the script in the following URL and look for missing constraints:

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

You need to run the script on all 4 databases below:

[DWRepository]
[DWDatamart]
[CMDwDatamart]
[OMDwDatamart]

0 Votes 0 ·

Can you please discribe steps in detail.
I run this https://dataedo.com/kb/query/sql-server/list-all-table-constraints script and chek constraints with errors in log
Message: UNION ALL view 'DWRepository.dbo.EntityManagedTypeFactvw' is not updatable because a partitioning column was not found.
I see all constraints with the start and end dates.
56271-image.png

What I'm missing?

0 Votes 0 ·
image.png (67.0 KiB)

There is no need to check it this way.

0 Votes 0 ·

You can execute the following USE statement and then execute the SQL script in the following URL, it can find all the constraints for you. You can add a WHERE clause to limit the output to '%Fact%' tables and check the constraints:

USE DWRepository

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

After that, repeat running the SQL script on each of the following 3 databases:

USE DwDataMart

USE CMDwDataMart

USE OMDwDataMart

@AndrewGordon-8681

0 Votes 0 ·

I don't understand what I should find?
Error is for DWRepository.dbo.EntityManagedTypeFactvw

use DWRepository
56584-image.png
USE DwDataMart
56605-image.png
USE CMDwDataMart there is no EntityManagedTypeFact
56517-image.png
USE OMDwDataMart there is no EntityManagedTypeFact
56565-image.png


0 Votes 0 ·
image.png (43.1 KiB)
image.png (41.9 KiB)
image.png (39.7 KiB)
image.png (40.6 KiB)

Sorry for the late reply, can you run the Get-SCDWJob powershell script in SCSM Data Warehouse Management Server to show which SCSM DW Jobs you got the failed result ?

I thought the steps are pretty straight forward as I already marked @DB-7386's reply as the Accepted Answer.

0 Votes 0 ·
Show more comments