dbo.QBM_PCustomSQLPropagate

1  create   procedure QBM_PCustomSQLPropagate (@ModuleToRestore varchar(3) = 'CCC' , @OnlyPropagateEmptyXTouched bit = 0 )  as begin declare @CurrentDeadlock_priority
2 int declare @ScriptName nvarchar(64) declare @ScriptType nvarchar(1) declare @initialContent nvarchar(max)   declare @UID_QBMCustomSQL varchar(38)  declare
3 @DebugSwitch int = 0 declare @Anzahl int     declare @work table ( UID_QBMCustomSQL varchar(38) collate database_default , ScriptName nvarchar(64) collate
4 database_default , ScriptType nvarchar(1) collate database_default , SortOrder int  , Scriptcode nvarchar(max) ) declare @nogo QBM_YParameterList declare
5 @ErrorBuffer QBM_YSessionError SET XACT_ABORT OFF BEGIN TRY set nocount on set quoted_identifier on select top 1 @CurrentDeadlock_priority = s.deadlock_priority
6 FROM sys.dm_exec_sessions s where s.session_id = @@spid select @CurrentDeadlock_priority = isnull(@CurrentDeadlock_priority, 0) set deadlock_priority 
78 insert into @nogo(Parameter1) values ('QBM_FGIObjectIsDropable') , ('QBM_PCustomSQLPropagate_Cont') , ('QBM_PCustomSQLPropagate') , ('QBM_PViewDrop')
8 , ('QBM_PFunctionDrop') , ('QBM_PProcedureDrop') , ('QBM_PTriggerDrop') , ('QBM_FSQObjectComment')  , ('QBM_PExecuteSQLWithRetry_LLP')  select @ScriptType
9 = N'U'  delete  @work  insert into @work(UID_QBMCustomSQL, ScriptName, ScriptType, SortOrder) select s.UID_QBMCustomSQL , s.ScriptName, s.ScriptType, 
10s.SortOrder from QBMCustomSQL s where ScriptType = @ScriptType and left(s.UID_QBMCustomSQL , 4) = @ModuleToRestore + '-'  and ( @OnlyPropagateEmptyXTouched
11 = 0 or ( @OnlyPropagateEmptyXTouched = 1 and trim(isnull(s.XTouched, '')) = '' ) )  select @anzahl = @@ROWCOUNT if @DebugSwitch > 0 begin print 'Anzahl U geladen '
12 + str(@anzahl)+ ' ' + dbo.QBM_FCVDatetimeToString(GETUTCDATE()) end while exists (select top 1 1 from @work) BEGIN select top 1 @UID_QBMCustomSQL = w.UID_QBMCustomSQL
13 , @ScriptName = w.ScriptName , @ScriptType = w.ScriptType from @work w order by w.SortOrder exec QBM_PCustomSQLPropagate_Cont @UID_QBMCustomSQL, @ScriptName
14 delete  @work where UID_QBMCustomSQL = @UID_QBMCustomSQL  update QBMCustomSQL set XTouched = 'I' from QBMCustomSQL c where c.UID_QBMCustomSQL = @UID_QBMCustomSQL
15 and trim(isnull(c.XTouched,'')) <> 'I'  END   select @ScriptType = N'C'  delete  @work insert into @work(UID_QBMCustomSQL, ScriptName, ScriptType, SortOrder
16) select s.UID_QBMCustomSQL , s.ScriptName, s.ScriptType, s.SortOrder from QBMCustomSQL s where ScriptType = @ScriptType and left(s.UID_QBMCustomSQL , 
174) = @ModuleToRestore + '-'  and ( @OnlyPropagateEmptyXTouched = 0 or ( @OnlyPropagateEmptyXTouched = 1 and trim(isnull(s.XTouched, '')) = '' ) )  select
18 @anzahl = @@ROWCOUNT if @DebugSwitch > 0 begin print 'Anzahl C geladen ' + str(@anzahl) + ' ' + dbo.QBM_FCVDatetimeToString(GETUTCDATE()) end while exists
19 (select top 1 1 from @work) BEGIN select top 1 @UID_QBMCustomSQL = w.UID_QBMCustomSQL , @ScriptName = w.ScriptName , @ScriptType = w.ScriptType from @work
20 w order by w.SortOrder exec QBM_PCustomSQLPropagate_Cont @UID_QBMCustomSQL, @ScriptName delete  @work where UID_QBMCustomSQL = @UID_QBMCustomSQL  update
21 QBMCustomSQL set XTouched = 'I' from QBMCustomSQL c where c.UID_QBMCustomSQL = @UID_QBMCustomSQL and trim(isnull(c.XTouched,'')) <> 'I'  END   select 
22@ScriptType = N'I'  delete  @work insert into @work(UID_QBMCustomSQL, ScriptName, ScriptType, SortOrder) select s.UID_QBMCustomSQL , s.ScriptName, s.ScriptType
23, s.SortOrder from QBMCustomSQL s where ScriptType = @ScriptType and left(s.UID_QBMCustomSQL , 4) = @ModuleToRestore + '-'  and ( @OnlyPropagateEmptyXTouched
24 = 0 or ( @OnlyPropagateEmptyXTouched = 1 and trim(isnull(s.XTouched, '')) = '' ) )  select @anzahl = @@ROWCOUNT if @DebugSwitch > 0 begin print 'Anzahl I geladen '
25 + str(@anzahl) + ' ' + dbo.QBM_FCVDatetimeToString(GETUTCDATE()) end while exists (select top 1 1 from @work) BEGIN select top 1 @UID_QBMCustomSQL = w.UID_QBMCustomSQL
26 , @ScriptName = w.ScriptName , @ScriptType = w.ScriptType from @work w order by w.SortOrder exec QBM_PCustomSQLPropagate_Cont @UID_QBMCustomSQL, @ScriptName
27 delete  @work where UID_QBMCustomSQL = @UID_QBMCustomSQL  update QBMCustomSQL set XTouched = 'I' from QBMCustomSQL c where c.UID_QBMCustomSQL = @UID_QBMCustomSQL
28 and trim(isnull(c.XTouched,'')) <> 'I'  END   if @DebugSwitch > 0 begin print 'start QBM_PViewBuild ' + dbo.QBM_FCVDatetimeToString(GETUTCDATE()) end 
29if @ModuleToRestore = 'CCC' begin exec QBM_PViewBuild end if @DebugSwitch > 0 begin print 'ende QBM_PViewBuild' + dbo.QBM_FCVDatetimeToString(GETUTCDATE
30()) end    delete  @work insert into @work(UID_QBMCustomSQL, ScriptName, ScriptType, SortOrder, Scriptcode) select s.UID_QBMCustomSQL, s.ScriptName, case
31 when s.ScriptType = N'V' then N'V' else N'F' end, s.SortOrder, s.Scriptcode from QBMCustomSQL s left outer join @nogo n on s.ScriptName = n.Parameter1
32 where ScriptType in (N'N', N'F', N'V') and left(s.UID_QBMCustomSQL , 4) = @ModuleToRestore + '-' and n.Parameter1 is null  and ( @OnlyPropagateEmptyXTouched
33 = 0 or ( @OnlyPropagateEmptyXTouched = 1 and trim(isnull(s.XTouched, '')) = '' ) )  select @anzahl = @@ROWCOUNT if @DebugSwitch > 0 begin print 'Anzahl V/F geladen '
34 + str(@anzahl) + ' ' + dbo.QBM_FCVDatetimeToString(GETUTCDATE()) end while exists (select top 1 1 from @work) BEGIN select top 1 @UID_QBMCustomSQL = w.UID_QBMCustomSQL
35 , @ScriptName = w.ScriptName , @ScriptType = w.ScriptType , @initialContent = w.Scriptcode from @work w order by w.SortOrder   exec QBM_PSQLCreate @ScriptName
36, @ScriptType, @initialContent, @UnComment = 0, @Unformat = 0 delete  @work where UID_QBMCustomSQL = @UID_QBMCustomSQL  update QBMCustomSQL set XTouched
37 = 'I' from QBMCustomSQL c where c.UID_QBMCustomSQL = @UID_QBMCustomSQL and trim(isnull(c.XTouched,'')) <> 'I'  END   delete  @work insert into @work(UID_QBMCustomSQL
38, ScriptName, ScriptType, SortOrder, Scriptcode) select s.UID_QBMCustomSQL , s.ScriptName, s.ScriptType, s.SortOrder, s.Scriptcode from QBMCustomSQL s 
39left outer join @nogo n on s.ScriptName = n.Parameter1 where ScriptType in (  N'P', N'T')  and left(s.UID_QBMCustomSQL , 4) = @ModuleToRestore + '-' and
40 n.Parameter1 is null  and ( @OnlyPropagateEmptyXTouched = 0 or ( @OnlyPropagateEmptyXTouched = 1 and trim(isnull(s.XTouched, '')) = '' ) )  select @anzahl
41 = @@ROWCOUNT if @DebugSwitch > 0 begin print 'Anzahl P/T geladen ' + str(@anzahl) + ' ' + dbo.QBM_FCVDatetimeToString(GETUTCDATE()) end while exists (select
42 top 1 1 from @work) BEGIN select top 1 @UID_QBMCustomSQL = w.UID_QBMCustomSQL , @ScriptName = w.ScriptName , @ScriptType = w.ScriptType , @initialContent
43 = w.Scriptcode from @work w order by w.SortOrder   exec QBM_PSQLCreate @ScriptName, @ScriptType, @initialContent, @UnComment = 0, @Unformat = 0 delete
44  @work where UID_QBMCustomSQL = @UID_QBMCustomSQL  update QBMCustomSQL set XTouched = 'I' from QBMCustomSQL c where c.UID_QBMCustomSQL = @UID_QBMCustomSQL
45 and trim(isnull(c.XTouched,'')) <> 'I'  END    if @ModuleToRestore = 'CCC' begin if @DebugSwitch > 0 begin print 'start QBM_PDatabasePrepareCheck1' + 
46dbo.QBM_FCVDatetimeToString(GETUTCDATE()) end exec QBM_PDatabasePrepareCheck1 if @DebugSwitch > 0 begin print 'ende QBM_PDatabasePrepareCheck1' + dbo.QBM_FCVDatetimeToString
47(GETUTCDATE()) end end END TRY BEGIN CATCH declare @ErrorMessage nvarchar(4000) = error_message() delete @ErrorBuffer insert into @ErrorBuffer (ErrorMessage
48, ErrorSeverity, ErrorState, ErrorNumber , ProcedureName, ProcedureLine, MessageDate , GenProcID, RepeatCounter, IsReThrow, SourceCode) select @ErrorMessage
49,ERROR_SEVERITY(), ERROR_STATE(), ERROR_NUMBER() , ERROR_PROCEDURE() , ERROR_LINE(), GETUTCDATE() , null, 0, 0, @ScriptName exec QBM_PSessionErrorAdd @ErrorBuffer
50  RAISERROR (@ErrorMessage, 18, 1) WITH NOWAIT END CATCH endLabel:  set deadlock_priority @CurrentDeadlock_priority  return end 
51