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 ·
SJM-7008 avatar image
0 Votes"
SJM-7008 answered SJM-7008 commented

Microsoft have a fix for this internally but you will need to raise a support case with Microsoft.

I have not yet implemented or tested the fix, but I will try reporting back our results.

To summarise:

The cause of the issue is that the DateDim in Service Manager ends in December 2020
This issue was resolved in 2016 UR5 and should ONLY be present in versions earlier than that (including 1801)


Regards

SJM

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

@SJM-7008 i suppose it's fixed also at SCSM 1807 considering this point mentioned on the list to the release :

  • DateDim tables in DWDataMart are now extended until December 31, 2050.

(same as 2016 UR5)

will give it a try updating from 1801 to 1807 in a mean time.
To wing 2019 upgrade is more complex, though seems we had finally started to push things to it. we'll see how it goes.



0 Votes 0 ·
SJM-7008 avatar image SJM-7008 AntonTolmachev-1615 ·

If that was indeed mentioned in the release notes for 1807 then upgrading from 1801 to 1807 is certainly an option for you to consider.

0 Votes 0 ·
Abdellah-9266 avatar image
0 Votes"
Abdellah-9266 answered Abdellah-9266 edited

Hi !

After 5 days of tests and checking all solutions here I resolved the problem of all jobs except the "Transform.Common", but at least reports are working now.

Here is how I did to resolve the incident, the procedure I followed is below :

(By the way! special thanks to @DB-7386, @WyattWong-6731, @SalimAssaf-7299, @CSharpConner-9289, @mohammadsaad-5988, @JimFinke-6249 for their efforts and for the good work :-) )

1- I restored a save snapshot of the DWH server before the first incident that Mr. @DB-7386 has put the first solution for, and I ran it on safe mode.
2- I disabled SCSM services.
3- I opened the SQL Server Management and I ran those commands :

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

4- I used the three scripts attached on bottom to fix Dec 2020, Jan and Feb 2021 constraints (Note that the third one didn't work on DWRepository.dbo because there are no Feb tables):

57156-image.png
57066-image.png
57077-image.png

5-After that I restarted the SCSM DWH and SCSM Servers ,now the reports are working .


I hope this can be helpful.


image.png (550.5 KiB)
image.png (542.1 KiB)
image.png (53.5 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Actually there is no need to restore from any snapshot, run in safe mode and disable SCSM service. You just need to execute the scripts to fix the constraints and re-run the SCSM DW Jobs.

0 Votes 0 ·
mohammadsaad-5988 avatar image
0 Votes"
mohammadsaad-5988 answered WyattWong-6731 commented

Hi,

We are encountering one issue, and it is out of constraint problem but anyone has any idea where to look at ?

In the webfront/vanaylst portal , support group is showing something else and in the report database it is showing different name.

which means report database support group column are not updating, anyone has any idea for this issue

?58141-portal.png
58132-report-database-excel.png



portal.png (143.0 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Can you run the Get-SCDWJob powershell cmdlet in SCSM Data Warehouse server ? If it is a different issue, you may consider to start a new post of your own.

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

@AndrewGordon-8681 I was unable to locate your post. You may contact me directly at

wyattwong @ outlook . com

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

Frustrating computers not doing what I'm asking. I'll try again here.58736-cmdwdatamart.txt58822-dwdatamart.txt58806-dwrepository.txt58700-omdwdatamart.txt



I'll send this to your e-mail again to cover both contingencies.

0 Votes 0 ·
cmdwdatamart.txt (46.6 KiB)
dwdatamart.txt (344.3 KiB)
dwrepository.txt (64.3 KiB)
omdwdatamart.txt (35.0 KiB)

I sampled check your CMDWDataMart database and found the following constraints are incorrect

dbo.ComputerHostsLogicalDiskFact_2018_Jan Table Check constraint ComputerHostsLogicalDiskFact_2018_Jan_Chk ([DateKey]<=(20180131))
dbo.ComputerHostsOperatingSystemFact_2018_Jan Table Check constraint ComputerHostsOperatingSystemFact_2018_Jan_Chk ([DateKey]<=(20180131))
dbo.ComputerHostsProcessorFact_2018_Jan Table Check constraint ComputerHostsProcessorFact_2018_Jan_Chk ([DateKey]<=(20180131))
dbo.DeployedComputerRunsWindowsComputerFact_2018_Jan Table Check constraint DeployedComputerRunsWindowsComputerFact_2018_Jan_Chk ([DateKey]<=(20180131))
dbo.GroupContainsConfigItemFact_2018_Jan Table Check constraint GroupContainsConfigItemFact_2018_Jan_Chk ([DateKey]<=(20180131))
dbo.ServiceContainsConfigItemFact_2018_Jan Table Check constraint ServiceContainsConfigItemFact_2018_Jan_Chk ([DateKey]<=(20180131))

0 Votes 0 ·

Yes, I tried correcting those but when I ran the script to do so, SQL output attached.58927-computerhostslogicaldiskfact-2018-jan.txt


0 Votes 0 ·

However, I am not sure if it is the causing your data warehouse jobs failed. If they are really the issues, then you should have encountered Data Warehouse jobs failed issue back in 2018 Jan.

However, you can run the following SQL scripts to fix the constraints problem for ComputerHostsLogicalDiskFact_2018_Jan table

ALTER TABLE [dbo].[ComputerHostsLogicalDiskFact_2018_Jan] DROP CONSTRAINT [ComputerHostsLogicalDiskFact_2018_Jan_Chk];
ALTER TABLE [dbo].[ComputerHostsLogicalDiskFact_2018_Jan] WITH CHECK ADD CONSTRAINT [ComputerHostsLogicalDiskFact_2018_Jan_Chk] CHECK (([DateKey]>=(20180101) AND [DateKey]<=(20180131)));
ALTER TABLE [dbo].[ComputerHostsLogicalDiskFact_2018_Jan] CHECK CONSTRAINT [ComputerHostsLogicalDiskFact_2018_Jan_Chk];


0 Votes 0 ·

Thank you. Tried that. Sadly, error.

Msg 3728, Level 16, State 1, Line 1
'ComputerHostsLogicalDiskFact_2018_Jan_Chk' is not a constraint.
Msg 3727, Level 16, State 0, Line 1
Could not drop constraint. See previous errors.

We've been running Service Manager since October, 2012, successfully until this nightmare started seven weeks ago. Maybe this is final straw so that we can dump SM and move onto something new.

0 Votes 0 ·

@AndrewGordon-8681 I have successfully assisted you and resolved your SCSM DW Jobs failed issues by e-mail, thank you very much for your patience.

1 Vote 1 ·

Can you manually delete the constraint from the Object Inspector in SQL Server Management Studio ?

59042-image.png


0 Votes 0 ·
image.png (13.7 KiB)
Show more comments

I found a post below:

https://dba.stackexchange.com/questions/115637/the-alter-table-statement-conflicted-with-the-check-constraint

According to the answer, if you encounter error when you execute "ALTER TABLE ...... WITH CHECK ADD CONSTRAINT .... CHECK ....." command. That could mean at least 1 row in your dbo.ComputerHostsLogicalDiskFact_2018_Jan table have DateKey value that violate the constraint condition of:

([DateKey] >= (20180101) AND [DateKey] <= (20180131))

So could you do a SELECT statement on dbo.ComputerHostsLogicalDiskFact_2018_Jan table to see if there is any DateKey value which is outside the above date range ?

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

Hey again, folks. Almost two months passed after the nasty error with Dates of Dec 2020 and Jan 2021 was fixed, so far so good, all was quiet.
Recently a new little gem popped up, not 100% percent sure if it's correlated with the Dates initial cause, on the other hand again.. damn Dec 2020 is involved. Got this one at DW server EventLog:
Unhandled exception in data warehouse maintenance:
Work item: 198356596
Maintenance action: PerformWarehouseGrooming Exception details:
Exception message: ErrorNumber="50000" Message="ErrorNumber="50000" Message="ErrorNumber="547" Message="The ALTER TABLE statement conflicted with the CHECK constraint "ComputerHasPrimaryUserFact_2020_Dec_Chk". The conflict occurred in database "DWRepository", table "dbo.ComputerHasPrimaryUserFact_2020_Dec", column 'DateKey'." Severity="16" State="0" ProcedureName="(null)" LineNumber="1" Task="Executing CHKScriptTemplate"" Severity="18" State="0" ProcedureName="DropCheckConstraintForTable" LineNumber="145" Task="Opening MIN Check constraint for the next Partition"" Severity="18" State="0" ProcedureName="DropPartition" LineNumber="108" Task="Executing groomingStoredProcedure: EXEC etl.DropPartition @WarehouseEntityId=@WarehouseEntityId, @WarehouseEntityType=@WarehouseEntityType, @EntityGuid=@EntityGuid, @PartitionId=@PartitionId, @GroomActiveRelationship=1"

as per @WyattWong-6731 advice i've checked table dbo.ComputerHasPrimaryUserFact_2020_Dec (since the error contains "The ALTER TABLE statement conflicted with the CHECK constraint" as well) to see if any DateKey value for whatever reason is out of the constraint condition (which is ([DateKey]>=(20201201) AND [DateKey]<=(20201231)) - but it's NOT, all the values are inside the range.
All the DW Jobs in overall (including DWMaintenance itself) DO complete Successfully.
Not sure at all for now why this is happening and the first Error event was dropped accurately on 1st March..duh.
Found similar older thread correlated unhandled-exception-in-data-warehouse-maintenance seems someone had that too and couldn't figure out what it is, though all was working in overall as well.
If someone experiences this as well or got any clue - would be highly appreciated.


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

In the etl.DropCheckConstraintForTable Stored Procedure, you can find a large section of comments which include a series of EXEC etl.DropCheckConstraintForTable and ALTER TABLE statements.

Outside the comment block, the Stored Procedure try to build the @CHKScript and then execute it. In the @CHKScript variable, there is a RangeEndDate value, which is retrieved from etl.TablePartition table.

You can execute the following statement to find out RangeEndDate of 20201230 in the etl.TablePartition table:

====
USE [DWRepository]

SELECT * FROM etl.TablePartition
WHERE RangeEndDate = 20201230
====

Since the CHECK constraint end date is 20201231, you can execute the following statement to patch the etl.TablePartition table:

====
USE [DWRepository]

UPDATE etl.TablePartition
SET RangeEndDate = 20201231
WHERE RangeEndDate = 20201230
====

2 Votes 2 ·

After patching the etl.TablePartition, you can execute the following statement and the Grooming should finished without any errors:


USE [DWRepository]

EXEC etl.PerformGrooming



It will take a few minutes to execute and after successful execution, you should see some messages about which tables were groomed.

If you execute "EXEC etl.PerformGrooming" again, you should receive the message "Checking to break the loop. @maxPartitionsToGroom: IS NULL @tempEntityId: IS NULL @tempPartitionId: IS NULL" which means there are NO more tables that need to be groomed.

2 Votes 2 ·

Good day ! First of all a BIG thank you for this great follow up. I've followed your TS plan step by step and it was just the case. Executing UPDATE of RangeEndDate on etl.TablePartition fixed 61 row with '20201230' Dates and after that Grooming ran smoothly and did it's thing, no initial error was spotted repeating after that. All good. And i think it'll help others, i'm more than sure many ppl may have this error shown up as well. Again great TS steps, your help appreciated much with this.

1 Vote 1 ·

You are welcome. Your discovery on the Grooming Error in the SCSM DW Operations Manager Event Log is very valuable. It was fun for me to troubleshoot the issue and that I was able to find out the root cause and resolve the issue.

I checked that there are no more Grooming Errors in the SCSM DW Operations Manager Event Log after fixing the Grooming Error issue.

0 Votes 0 ·

What does your "Get-SCDWJob" looks like. Did you see any SCSM Data Warehouse Jobs show error and stuck in the same BatchId ?

0 Votes 0 ·

Good day, i've attached the DWJobs screen, no any errors (except mentioned one in the EventLog, i assume it comes during DWMaintenance task hence repeats and logged hourly) and no stuck Batches as well, as per EventLog all jobs complete Successfully in overall (DWMaintenance too)

74232-untitled1.png


0 Votes 0 ·
untitled1.png (108.7 KiB)

You may keep monitoring the "Get-SCDWJob" for a few days and take note of any changes in the BatchId. If the SCSM DW Jobs are working properly, you should see the BatchId change. Also double check the "StartTime" and "EndTime" column. If a SCSM DW Job "StartTime" was stuck in yesterday's date or a few dates before, then that means your specific SCSM DW Jobs was stuck and need to resolve.

In your current situation, I think you don't need to do anything as you only saw the errors in the System Center Operations Manager Event Log.

1 Vote 1 ·
Show more comments

I have finally resolved the problem.

First of all, the etl.PerformGrooming Stored Procedure will retrieve the value of "GroomingStoredProcedure" from etl.WarehouseEntityGroomingInfo table and then execute it. The GroomingStoredProcedure looks like the following:

======
EXEC etl.DropPartition @WarehouseEntityId=@WarehouseEntityId, @WarehouseEntityType=@WarehouseEntityType, @EntityGuid=@EntityGuid, @PartitionId=@PartitionId, @GroomActiveRelationship=1
=======

Then in the etl.DropPartition Stored Procedure, it will perform a series of process, including the execution of another Stored Procedure etl.DropCheckConstraintForTable. One of the parameters to pass into the etl.DropCheckConstraintForTable Stored Procedure is the @followingPartitionName. The value of the @followingPartitionName was retrieved from a table called etl.TablePartition.

1 Vote 1 ·
BillCrum-1926 avatar image
0 Votes"
BillCrum-1926 answered BillCrum-1926 commented

So I was very late to the game on this, We dont use SCSM for much anymore, but I just happened to do some windows updates and noticed my DW was broken. Research lead me here, and pursuing the solutions here did not help me much, but I must also say this DW has been jacked up for 2 years, so it has alot MORE wrong with it to, however I couldnt free myself of these unique errors, so I kept working.

Well, I think I made it worse by customizing the script some, but all in all I couldnt find any bad constraints myself, so I went in search elsewhere and ran across this script below, which was from Microsoft, and it is far better than the ones here from what I can tell.

Note: There is a Boolean at the top, if you set it at 0 it only runs in practice mode... BUT it will give you a message that there are no errors, THIS IS MISLEADING. I checked all my DBs and it showed good, so I flipped the boolean to 1 to actually let it run, and it reports on how many breaks you have, and I had a LOT. It will tell you to rerun it to check again, and when it runs a pass without any fixes, it does not put all those Altars on the page, just tells you it was good.

Errors:
104059-image.png


Clean Database:
104060-image.png



image.png (151.3 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BillCrum-1926 avatar image
0 Votes"
BillCrum-1926 answered BillCrum-1926 edited

104133-image.png




Here is a screenshot of a clean run of the script with it in ACTIVE mode.... Notice we ONLY see the successful no errors messages? When you run it in test mode, it says no errors also, but for me it had TONs of lines of commands that would fix problems also.

I am having issues getting the upload to work on this 10kb txt file :(, but if I cant upload it, I'll post it somewhere and link it.

I hope this helps others like me who arent very adept at SQL and understanding everything involved, I do not know yet if my jobs run, Im just kicking them off, but I do know this script fixed over 1k errors on my DW after I couldnt find ANY bad constraints with my terrible skills.


image.png (36.7 KiB)
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.

BillCrum-1926 avatar image
0 Votes"
BillCrum-1926 answered BillCrum-1926 commented
 declare @fixFactDiscrepancies bit =0    -- set this to 1 for real corrections, but Please take a database backup before !!!
 --set it to 0 to only show the discrepancies
 -------------------------------------------------------------------------------
 set nocount on
 if @fixFactDiscrepancies = 0 
     print 'Correcting DateDim rows, Fact TABLEs and VIEWs, if discrepancies found ...';
 else
     print 'Read-only mode...'    
 print ''
    
 declare @dec31Exists bit
 set @dec31Exists=0
 select @dec31Exists=1 from DateDim where DateKey=20201231
 if @dec31Exists = 0 and @fixFactDiscrepancies = 1
 begin
     print 'inserting missing Dec 31 2020 row into DateDim'
     insert into DateDim values (20201231,    '20201231',    'Thursday',    31,    53,    'December',    12,    2020,    'Q4',    'Q2',    6,    2021,    0,    1,    1)
 end
    
 declare @dateCountAfter2021 int
 set @dateCountAfter2021=0
 select @dateCountAfter2021=count(*) from DateDim where DateKey>=20210101 --10956
 if @dateCountAfter2021 < 10956 and @fixFactDiscrepancies = 1
 begin
     print 'running PopulateDateDim'
     EXEC PopulateDateDim '20210101', '20501231'
 end
    
 declare @factCountTotal int = 0, @factCountCorrected int = 0
 declare @factViewCountTotal int = 0, @factViewCountCorrected int = 0
 declare @Months table (MonthName char(3),MonthValue tinyint, LastDay tinyint)
 insert into @Months values 
 ('Jan',1,31),
 ('Feb',2,28),
 ('Mar',3,31),
 ('Apr',4,30),
 ('May',5,31),
 ('Jun',6,30),
 ('Jul',7,31),
 ('Aug',8,31),
 ('Sep',9,30),
 ('Oct',10,31),
 ('Nov',11,30),
 ('Dec',12,31)
 declare @factName sysname
 declare c cursor local FORWARD_ONLY READ_ONLY for
     select distinct left(name,datalength(name)/2-13)+'vw' as name from sys.check_constraints where name like '%\_____\____\_Chk' escape '\' and definition like '%DateKey%' order by 1
 open c; while 1=1 begin; fetch c into @factName; if @@FETCH_STATUS<>0 break;
     set @factViewCountTotal=@factViewCountTotal+1
    
     declare @currentView nvarchar(max), @isViewCorrect bit, @selectClause nvarchar(max) 
     set @isViewCorrect = 1
     set @selectClause=''
     select @currentView=definition from sys.objects o join sys.sql_modules m on m.object_id = o.object_id where o.object_id = object_id('dbo.'+ @factName) and o.type = 'V' 
    
     set @factName=left(@factName,datalength(@factName)/2-2)
     declare @factTableName sysname
     declare @factTables table (name sysname, [Year] smallint, [Month] tinyint, YearMonth int)
     delete @factTables
     insert into @factTables (name) select name from sys.tables where name like @factName + '\_____\____' escape '\' order by name;    
    
     declare c2 cursor local FORWARD_ONLY READ_ONLY for
         select name from @factTables;
     open c2; while 1=1 begin; fetch c2 into @factTableName; if @@FETCH_STATUS<>0 break;        
         declare @Year smallint, @Month tinyint, @YearMonth int, @YearName char(4), @MonthName char(3)
         set @MonthName=RIGHT(@factTableName,3)
         set @YearName=RIGHT(@factTableName,8); set @YearName=left(@YearName,4)
         set @year=cast(@YearName as smallint)
         select @Month=MonthValue from @Months where MonthName=@MonthName
         update @factTables
         set [Year]=@Year, [Month]=@Month, YearMonth=@Year*100+@Month
         where name=@factTableName
     end; close c2; deallocate c2;
     declare @minYear smallint, @minMonth tinyint, @maxYear smallint, @maxMonth tinyint, @minYearMonth int, @maxYearMonth int, @prevYearMonth int, @nextAvailableYear smallint, @nextAvailableMonth tinyint, @prevMonthLastDay int
     select @minYear=min(YearMonth)/100, @minMonth=min(YearMonth) % 100, @maxYear=max(YearMonth)/100, @maxMonth=max(YearMonth) % 100, @minYearMonth=min(YearMonth), @maxYearMonth=max(YearMonth)    from @factTables
     declare @isFirstIteration bit =1
     declare @correctView nvarchar(max) 
     set @correctView= ''
     declare c3 cursor local FORWARD_ONLY READ_ONLY for
         select name, [Year], [Month], YearMonth from @factTables order by YearMonth
     open c3; while 1=1 begin; fetch c3 into @factTableName, @Year, @Month, @YearMonth; if @@FETCH_STATUS<>0 break;
         set @factCountTotal=@factCountTotal+1
         declare @chkStartDate int, @chkEndDate int
    
         if @YearMonth = @maxYearMonth
         begin
             set @chkStartDate = @YearMonth*100 + 1
             set @chkEndDate=null
         end
         else
         begin            
             select top 1 @nextAvailableYear=[Year], @nextAvailableMonth=[Month]
             from @factTables
             where YearMonth>@YearMonth
             order by YearMonth        
             if @nextAvailableMonth = 1    set @prevMonthLastDay=(@nextAvailableYear-1)*10000+1231
             else    set @prevMonthLastDay=(@nextAvailableYear*10000) + ((@nextAvailableMonth-1)*100) + (select LastDay from @Months where MonthValue=@nextAvailableMonth-1) 
                
             if @isFirstIteration = 1  
                 set @chkStartDate = null
             else 
                 set @chkStartDate = @YearMonth*100 + 1
    
             set @chkEndDate = @prevMonthLastDay
         end    
         if @chkEndDate % 100 = 28                    
             if ((@chkEndDate / 10000) % 4) = 0    set @chkEndDate = @chkEndDate+1    
            
         --1. check constraint
         declare @currentChk nvarchar(max)
         set @currentChk=''
         select @currentChk=definition from sys.check_constraints where name = @factTableName + '_Chk'
         set @currentChk = REPLACE(@currentChk,'(','')
         set @currentChk = REPLACE(@currentChk,')','')
    
         declare @correctChk nvarchar(max) 
         set @correctChk = ''
         if @chkStartDate is not null
             set @correctChk = '[DateKey]>='+ cast(@chkStartDate as nvarchar(max))
         if @chkStartDate is not null  and @chkEndDate is not null
             set @correctChk = @correctChk + ' AND '
         if @chkEndDate is not null
             set @correctChk = @correctChk + '[DateKey]<='+ cast(@chkEndDate as nvarchar(max))
    
         declare @chkScript nvarchar(max) 
         set @chkScript= ''
         if @currentChk != @correctChk
         begin
             print '--current '+ @factTableName+'_chk: ' + @currentChk
    
             if @currentChk != '' 
                 set @chkScript = 'ALTER TABLE dbo.'+ @factTableName +' DROP CONSTRAINT '+ @factTableName +'_Chk; '            
                        
             set @chkScript = @chkScript + ' ALTER TABLE [dbo].['+ @factTableName +']  WITH CHECK ADD  CONSTRAINT ['+ @factTableName +'_Chk] CHECK  ('+ @correctChk +'); ALTER TABLE [dbo].['+ @factTableName +'] CHECK CONSTRAINT ['+ @factTableName +'_Chk]'
             print @chkScript
             if @fixFactDiscrepancies = 1    
             begin try
                 exec sp_executesql @chkScript
                 set @factCountCorrected = @factCountCorrected +1
             end try
             begin catch
                 print 'Error happened with: ' + @chkScript
                 print ERROR_MESSAGE()                
             end catch            
         end
    
         --2. check in vw
         set @selectClause = ''
         select @selectClause=@selectClause+','+name from sys.columns where object_id=(select object_id from sys.views where name=@factName+'vw') order by column_id
         set @selectClause = 'SELECT ' + SUBSTRING(@selectClause,2,8000) + ' FROM ' + @factTableName 
         if @isFirstIteration = 0
             set @correctView = @correctView + ' UNION ALL '
         set @correctView = @correctView + @selectClause
    
         if CHARINDEX(@factTableName, @currentView)=0
         begin
             set @isViewCorrect=0
             print @factTableName +' not in view.'
         end
    
         if @isFirstIteration =1 set @isFirstIteration=0
     end; close c3; deallocate c3;
    
     if @isViewCorrect=0        
     begin
         declare @dropView nvarchar(max) 
         select @dropView='DROP VIEW [dbo].['+ @factName+'vw' +']'
         if @fixFactDiscrepancies = 1     
             begin try
                 exec sp_executesql @dropView
             end try
             begin catch
                 print 'Error happened with: ' + @dropView
                 print ERROR_MESSAGE()                
             end catch            
         else 
             print @dropView
    
         set @correctView = 'CREATE VIEW [dbo].['+ @factName+'vw' +'] AS '+@correctView 
         if @fixFactDiscrepancies = 1    
             begin try
                 exec sp_executesql @correctView
                 set @factViewCountCorrected = @factViewCountCorrected +1
             end try
             begin catch
                 print 'Error happened with: ' + @correctView
                 print ERROR_MESSAGE()                
             end catch    
         else 
             print @correctView
            
     end
    
 end; close c; deallocate c;
    
 declare @result nvarchar(max)
 print ''
 print '*****************************************************************'
 print '----- R E S U L T   for database:  ' + DB_NAME() 
 print '*****************************************************************'
 print ''
    
 if @dec31Exists=0 or @dateCountAfter2021 < 10956
 begin    
     if @fixFactDiscrepancies = 0
         print 'Issues in DateDim found. To fix them, set @fixFactDiscrepancies to 1 and then run the script again. Please take a database backup before !!!'
     else
         print 'Issues in DateDim found. They have been all corrected. Run the script again for verification.'
 end
 else
     print 'No issues found in DateDim.'
    
 -------------------
 print ''
 -------------------
 if @factCountCorrected > 0
 begin
     set @result = cast(@factCountCorrected as nvarchar(max)) + ' of ' + cast(@factCountTotal as nvarchar(max)) + ' CONSTRAINTS in FACT tables are incorrect.'
     print @result
     if @fixFactDiscrepancies = 0
         print 'They have NOT been corrected. To fix them, set @fixFactDiscrepancies to 1 and then run the script again. Please take a database backup before !!!'
     else
         print 'They have been all corrected. Run the script again for verification.'
 end
 else
     print 'No CONSTRAINT discrepancies found in ' + cast(@factCountTotal as nvarchar(max)) + ' FACT tables.'
    
 -------------------
 print ''
 -------------------
  if @factViewCountCorrected > 0
 begin
     set @result = cast(@factViewCountCorrected as nvarchar(max)) + ' of ' + cast(@factViewCountTotal as nvarchar(max)) + ' VIEW definitions are incorrect.'
     print @result
     if @fixFactDiscrepancies = 0
         print 'They have NOT been corrected. To fix them, set @fixFactDiscrepancies to 1 and then run the script again. Please take a database backup before !!!'
     else
         print 'They have been all corrected. Run the script again for verification.'
 end
 else
     print 'No discrepancies found in ' + cast(@factViewCountTotal as nvarchar(max)) + ' FACT views.'
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

My DW Jobs are fixed, I am AMAZED!

So to sum up the simple fix.
1. Turn off your Microsoft Monitoring Agent at the DW Management Server, I also turned off the one at the DW. Disable your jobschedule first.
2. Check your DateKeys to make sure you added up through 2050,
3. Once you have your Dates right, and confirm that 12/31/2020 is in all your tables, you're ready to move on.
4. At this point it is time to fix your Constraints, So run the script I posted above.
5. You need to run the script on your 4 main databases: DWRepository, DWDataMart, OMCWDataMart & CMDWDataMart.
6. Start Monitoring Agent on Mgmt and DW Server.
7. I ran my DWRefresh Script to run the jobs in the right order, and it exits and reports when it sees a failure.
8. I ran all my main Jobs cleanly, and am up to my third Cube now which amazes me after not syncing in 18-24

0 Votes 0 ·
BillCrum-1926 avatar image
0 Votes"
BillCrum-1926 answered BillCrum-1926 commented

Cannot believe how well this worked! Keep in mind, all the scripts on this site have to be manually adjusted for every Month you need to correct. This script doesnt have to be touched, it clears up ALL TIME issues.


image.png (212.5 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I think your script is much more complicated than the script provided here (which was also provided by Microsoft). Anyway, as long you have fixed the DW issue, it should be fine. Have a nice day !

0 Votes 0 ·

It is more advanced, it isn't limited to 1 month per run, with manual changes in between each run. You run this one one time, and it searches ALL your records and fixes them. It also is designed by someone who knows exactly which files should and shouldn't have certain settings, as I added Check Constraints to several dozen files that I shouldnt have, and this software identified them and fixed them.

0 Votes 0 ·