dbo.QBM_PCheckSameCase_38

1     create   procedure QBM_PCheckSameCase_38 as begin declare @SQLcmd nvarchar(max) declare @cmdwork nvarchar(max) declare @parenttable nvarchar
2(64), @childtable nvarchar(64), @ParentColumn nvarchar(64), @ChildColumn nvarchar(64) declare @changed bit declare @CountItems int  declare @DebugLevel
3 char(1) = 'W' declare @Debugmessage nvarchar(4000) declare @DebugSwitch int = 0 declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare
4 @ElementIndex int declare @XUser nvarchar(64) = object_name(@@procid) declare @Xdate datetime = getutcdate() SET XACT_ABORT OFF BEGIN TRY    update QBMRelation
5 set XMarkedForDeletion = 0  , XDateUpdated = @Xdate , XUserUpdated = @XUser where isnull(XMarkedForDeletion, -1) <> 0  and QBMRelation.ParentExecuteBy
6 in ('T', 'D') update QBMRelation set XMarkedForDeletion += 1 , XDateUpdated = @Xdate , XUserUpdated = @XUser from QBMRelation tr where exists (select 
7top 1 1 from QBMRelation t1 where t1.relationid <> tr.relationid and tr.uid_childcolumn = tr.uid_parentcolumn and t1.XMarkedForDeletion = tr.XMarkedForDeletion
8 )  and tr.ParentExecuteBy in ('T', 'D')  select @SQLcmd = N'
9			if exists (select top 1 1
10						 from  @childtable, @parenttable p
11						   where @childtable.@childcolumn = p.@parentColumn 
12							 and @childtable.@childcolumn <> p.@parentColumn  collate SQL_Latin1_General_CP1_CS_AS /* 35127 CS*/
13						)
14			 begin
15				update @childtable set  @childtable.@childcolumn = p.@parentColumn #xwerte#
16				  from  @childtable, @parenttable p
17					   where @childtable.@childcolumn = p.@parentColumn 
18						 and @childtable.@childcolumn <> p.@parentColumn  collate SQL_Latin1_General_CP1_CS_AS /* 35127 CS*/
19			 end
20				'
21  insert into @ElementBuffer (Ident1, Ident2, Ident3, ObjectKey1) select rtrim(tr.parenttable), rtrim(tr.childtable), rtrim(tr.ParentColumn) as ParentColumn
22, rtrim(tr.ChildColumn) as ChildColumn from QBM_VQBMRelation tr    join DialogColumn ic with (readpast) on ic.UID_DialogTable = tr.UID_DialogTableParent
23 and ic.UID_DialogColumn = tr.UID_ParentColumn and ic.SchemaDataType like '%char%'  join dialogtable t1 with (readpast) on tr.UID_DialogTableParent = t1.UID_DialogTable
24 and t1.tabletype in ('T', 'B') join QBM_VSchemaColumns isp on isp.TABLE_NAME = tr.parenttable and isp.COLUMN_NAME = tr.ParentColumn join dialogtable t2
25 with (readpast) on tr.UID_DialogTableChild = t2.UID_DialogTable and t2.tabletype in ('T', 'B') join QBM_VSchemaColumns isc on isc.TABLE_NAME = tr.ChildTable
26 and isc.COLUMN_NAME = tr.ChildColumn where tr.ParentExecuteBy in ('T', 'D')  and t1.TableName not in (select TableName from QBM_VHeavyLoadTables  ) and
27 t2.TableName not in (select TableName from QBM_VHeavyLoadTables  ) order by tr.XMarkedForDeletion desc, parenttable asc, childtable asc select @ElementCount
28 = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @parenttable = bu.Ident1 , @childtable = bu.Ident2 , @ParentColumn
29 = bu.Ident3 , @ChildColumn = bu.ObjectKey1 from @ElementBuffer bu where bu.ElementIndex = @ElementIndex select @cmdwork = @SQLcmd select @cmdwork = replace
30(@cmdwork, N'@childtable', @childtable) select @cmdwork = replace(@cmdwork, N'@parenttable', @parenttable) select @cmdwork = replace(@cmdwork, N'@childcolumn'
31, @childcolumn) select @cmdwork = replace(@cmdwork, N'@parentcolumn', @parentcolumn) select @cmdwork = replace(@cmdwork, N'#xwerte#', case when dbo.QBM_FGIColumnExistsInSchema
32(@childtable, 'XDateUpdated') = 1 and @ChildColumn not like 'X[d,u][a,s][t,e][e,r]updated' then ', ' + @childtable +'.XDateUpdated = getutcdate(), '+@childtable+
33'.XUserUpdated =  ''' + object_name(@@procid) + '''' else '' end ) select @changed = 1   while @changed = 1 begin  exec @CountItems = QBM_PExecuteSQLWithRetry_LLP
34 @SQLStatement = @cmdwork , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0 if @CountItems
35 > 0 begin select @changed = 1 select @Debugmessage = @parenttable + ' -> ' + @childtable + ' Changed' if @DebugSwitch > 0 begin print @Debugmessage + 
36str(@CountItems) end else begin exec QBM_PJournal @Debugmessage, @@PROCID, 'D', @DebugLevel end end else begin select @changed = 0 select @Debugmessage
37 = @parenttable + ' -> ' + @childtable + ' OK' if @DebugSwitch > 0 begin print @Debugmessage end else begin exec QBM_PJournal @Debugmessage, @@PROCID, 
38'T', @DebugLevel end end end select @ElementIndex += 1 end  update QBMRelation set XMarkedForDeletion = 0 where isnull(XMarkedForDeletion, -1) <> 0 END
39 TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT
40 END CATCH end 
41