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

Question

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

Answer 1

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:

1. Backup all DW DBs
2. 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'
3. 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;

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

Answer 2

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

Answer 3

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.