dbo.QBM_PDBQueueReplGenProcID

1      create   procedure QBM_PDBQueueReplGenProcID (@UID_Task varchar(38) , @SlotNumber int , @ExecutionType int     , @CountParameter int ) as 
2begin  declare @object varchar(38) declare @SubObject varchar(38) declare @GenProcIDNew varchar(38) declare @GenProcIDFallBack varchar(38) = newid() declare
3 @AnzahlInGruppe int declare @Doppelte QBM_YMNTable   declare @ReplaceLimit int declare @Compresslimit int  declare @CountItems int  declare @DebugSwitch
4 int = 0 declare @deadlockcounter int declare @waittime float  declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex
5 int declare @Repair QBM_YHelperQueueRepair declare @Substitute QBM_YHelperQueueSubstitute declare @SlotNumberSource int declare @SlotNumberTarget int 
6declare @DBQueueToMove QBM_YDBQCSCurrentToMove declare @RowsMoved int SET XACT_ABORT OFF BEGIN TRY select @waittime = convert(float, @SlotNumber) * 0.15
7 if @DebugSwitch > 0 begin print 'im replacer angekommen, Trancount ' + str(@@trancount) print 'aktives Slot ' + str(@slotnumber) end select @ReplaceLimit
8 = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('QBM\DBQueue\GenProcIDReplaceLimit'), 6)        if @@rowcount = 0 begin  goto ende end if '' = dbo.QBM_FGIConfigparmValue
9 ('Common\ProcessState')  begin select @compresslimit = 1 end else begin select @compresslimit = @ReplaceLimit  end delete  @Repair    if @DebugSwitch 
10> 0 begin print 'ReplGenProcID vor Doppelte :' + nchar(9) + convert(nvarchar(64), GETUTCDATE(), 121) end  if @countparameter = 0 begin insert into @Doppelte
11 (UID_Element1 , UID_Element2 , XOrigin ) select '' as object , '' as subobject , CountItems from ( select Count(*) as CountItems from QBMDBQueueCurrent
12 with (readpast) where SlotNumber = @SlotNumber ) as x where x.CountItems > 1 select @CountItems = @@rowcount if @DebugSwitch > 0 begin print 'doppelte ermittelt für Parameter 0, Stückzahl '
13 + str(@CountItems) end end if @countparameter = 1 begin insert into @Doppelte (UID_Element1 , UID_Element2 , XOrigin ) select isnull(uid_parameter, ''
14) as object , '' as subobject , CountItems from ( select uid_parameter , Count(*) as CountItems from QBMDBQueueCurrent with (readpast) where SlotNumber
15 = @SlotNumber group by uid_parameter having count(*) > 1 ) as x select @CountItems = @@rowcount if @DebugSwitch > 0 begin print 'doppelte ermittelt für Parameter 1, Stückzahl '
16 + str(@CountItems) end end if @countparameter = 2 begin insert into @Doppelte (UID_Element1 , UID_Element2 , XOrigin ) select uid_parameter as object 
17, uid_SubParameter as subobject , CountItems from ( select uid_parameter , uid_Subparameter, Count(*) as CountItems from QBMDBQueueCurrent with (readpast
18) where SlotNumber = @SlotNumber group by uid_parameter, uid_SubParameter  having count(*) > 1 ) as x select @CountItems = @@rowcount if @DebugSwitch >
19 0 begin print 'doppelte ermittelt für Parameter 2, Stückzahl ' + str(@CountItems) end end if @CountItems = 0 begin if @DebugSwitch > 0 begin print 'ReplGenProcID keine Doppelte :'
20 + nchar(9) + convert(nvarchar(64), GETUTCDATE(), 121) end goto ende end if @DebugSwitch > 0 begin print 'ReplGenProcID nach Doppelte :' + nchar(9) + convert
21(nvarchar(64), GETUTCDATE(), 121) end  if @countParameter = 0 begin insert into @Repair (UID_DialogDBQueue, object, subobject, GenProcIDNew , GenProcIDOrigin
22, Xtouched, Generation ) select q.UID_DialogDBQueue, '', '', '' , case when isnull(q.GenProcID,'') = '' then @GenProcIDFallBack else q.GenProcID end , 
23'' , q.Generation from QBMDBQueueCurrent q with (readpast) join @Doppelte x on q.SlotNumber = @SlotNumber option (maxdop 1) select @CountItems = @@rowcount
24 end if @countParameter = 1 begin insert into @Repair (UID_DialogDBQueue, object, subobject, GenProcIDNew , GenProcIDOrigin, Xtouched, Generation ) select
25 q.UID_DialogDBQueue, x.UID_Element1 , '', '' , case when isnull(q.GenProcID,'') = '' then @GenProcIDFallBack else q.GenProcID end , '' , q.Generation 
26from QBMDBQueueCurrent q with (readpast) join @Doppelte x on x.UID_Element1  = isnull(q.uid_parameter,'') collate database_default and q.SlotNumber = @SlotNumber
27 option (maxdop 1) select @CountItems = @@rowcount end if @countParameter = 2 begin insert into @Repair (UID_DialogDBQueue, object, subobject, GenProcIDNew
28 , GenProcIDOrigin, Xtouched, Generation ) select q.UID_DialogDBQueue, x.UID_Element1 , x.UID_Element2 , '' , case when isnull(q.GenProcID,'') = '' then
29 @GenProcIDFallBack else q.GenProcID end , '' , q.Generation from QBMDBQueueCurrent q with (readpast) join @Doppelte x on x.UID_Element1  = isnull(q.uid_parameter
30,'') collate database_default and x.UID_Element2  = isnull(q.uid_subparameter,'') collate database_default and q.SlotNumber = @SlotNumber option (maxdop
31 1) select @CountItems = @@rowcount end  if @CountItems = 0 begin if @DebugSwitch > 0 begin print 'Replace keine Gruppen gefunden:' + convert(nvarchar(64
32), GETUTCDATE(), 121) end goto ende end      select @deadlockcounter = 150 while @deadlockcounter > 0 begin BEGIN TRY select @SlotNumberSource = @SlotNumber
33  select @SlotnumberTarget = 0 delete @DBQueueToMove  insert into @DBQueueToMove(UID_DialogDBQueue) select cul.UID_DialogDBQueue from @Repair cul exec 
34@RowsMoved = QBM_PDBQCS_CurrentMoveSlot @DBQueueToMove, @SlotNumberSource, @SlotnumberTarget          select @deadlockcounter = 0 END TRY BEGIN CATCH exec
35 QBM_PSessionErrorAdd default if dbo.QBM_FGISessionErrorIsDeadlock(default) = 1 begin exec QBM_PWaitForSeconds @waittime select @deadlockcounter -= 1 end
36 else begin RAISERROR ('', 18, 1) WITH NOWAIT end END CATCH end  exec QBM_PSessionErrorClean    delete  @Substitute  insert into @Substitute (GenProcIDOrigin
37, GenProcIDNew) select GenProcIDOrigin, GenProcIDNew from DialogProcessSubstitute s where exists (select top 1 1 from @Repair r where s.GenProcIDOrigin
38 = r.GenProcIDOrigin ) or exists (select top 1 1 from @Repair r where s.GenProcIDNew = r.GenProcIDOrigin ) option (maxdop 1)  if @DebugSwitch > 0 begin
39 print 'DialogProcessSubstitute initialbefüllung' end  insert into @Substitute (GenProcIDOrigin, GenProcIDNew) select GenProcIDOrigin, GenProcIDNew from
40 DialogProcessSubstitute s where exists ( select top 1 1 from @Substitute sx where sx.GenProcIDNew = s.GenProcIDNew ) and not exists (select top 1 1 from
41 @Substitute x where x.GenProcIDOrigin = s.GenProcIDOrigin and x.GenProcIDNew = s.GenProcIDNew )  update @Substitute set CountOrigin = x.CountItems from
42 @Substitute s, ( select GenProcIDNew , count(*) as CountItems from @Substitute group by GenProcIDNew ) as x where s.GenProcIDNew = x.GenProcIDNew  if 
43@DebugSwitch > 0 begin print ' vor Substitute-Anwendung' end  if @CountParameter = 0 begin insert into @Repair (UID_DialogDBQueue, object, subobject, GenProcIDNew
44, GenProcIDOrigin, Xtouched, Generation ) select newid(), x.object, x.subobject, x.GenProcIDNew, x.GenProcIDOrigin, '', x.Generation from ( select '' as
45 object, '' as subobject , '' as GenProcIDNew, s.GenProcIDOrigin, MAX(r.Generation) as Generation from @Repair r join @Substitute s on r.GenProcIDOrigin
46 = s.GenProcIDNew where Not exists (select top 1 1 from @Repair r2 where r2.GenProcIDOrigin = s.GenProcIDOrigin ) group by s.GenProcIDOrigin ) as x option
47 (maxdop 1) end if @CountParameter = 1 begin insert into @Repair (UID_DialogDBQueue, object, subobject, GenProcIDNew, GenProcIDOrigin, Xtouched, Generation
48 ) select newid(), x.object, x.subobject, x.GenProcIDNew, x.GenProcIDOrigin, '', x.Generation from ( select r.object, '' as subobject , '' as GenProcIDNew
49, s.GenProcIDOrigin, MAX(r.Generation) as Generation from @Repair r join @Substitute s on r.GenProcIDOrigin = s.GenProcIDNew where Not exists (select top
50 1 1 from @Repair r2 where r2.object = r.object and r2.GenProcIDOrigin = s.GenProcIDOrigin ) group by r.object, s.GenProcIDOrigin ) as x option (maxdop
51 1) end if @CountParameter = 2 begin insert into @Repair (UID_DialogDBQueue, object, subobject, GenProcIDNew, GenProcIDOrigin, Xtouched, Generation ) select
52 newid(), x.object, x.subobject, x.GenProcIDNew, x.GenProcIDOrigin, '', x.Generation from ( select r.object, r.subobject, '' as GenProcIDNew, s.GenProcIDOrigin
53, MAX(r.Generation) as Generation from @Repair r join @Substitute s on r.GenProcIDOrigin = s.GenProcIDNew where Not exists (select top 1 1 from @Repair
54 r2 where r2.object = r.object and r2.subobject = r.subobject and r2.GenProcIDOrigin = s.GenProcIDOrigin ) group by r.object, r.subobject, s.GenProcIDOrigin
55 ) as x option (maxdop 1) end  if @DebugSwitch > 0 begin print ' nach Substitute-Anwendung' end  update @Repair set GenProcIDNew = '' , GenProcIDOrigin
56 = s.GenProcIDOrigin from @Repair r join @Substitute s on r.GenProcIDOrigin = s.GenProcIDNew option (maxdop 1) if @DebugSwitch > 0 begin print ' nach Update-Anwendung'
57 end     if @CountParameter = 0 begin if exists (select top 1 1 from @Repair group by GenProcIDorigin having count(*) > 1 ) begin delete  @Repair from 
58@Repair qr, (select min(uid_dialogdbqueue) as uid_dialogdbqueue ,GenProcIDOrigin from @Repair group by GenProcIDorigin having count(*) > 1 ) as x where
59 qr.GenProcIDorigin = x.GenProcIDorigin and qr.uid_dialogdbqueue <> x.uid_dialogdbqueue end  end  if @CountParameter = 1 begin if exists (select top 1 
601 from @Repair group by object, GenProcIDorigin having count(*) > 1 ) begin delete  @Repair from @Repair qr , (select min(uid_dialogdbqueue) as uid_dialogdbqueue
61 , object, GenProcIDOrigin from @Repair group by object, GenProcIDorigin having count(*) > 1 ) as x where qr.Object = x.object and qr.GenProcIDOrigin =
62 x.GenProcIDorigin and qr.uid_dialogdbqueue <> x.uid_dialogdbqueue end  end  if @CountParameter = 2 begin if exists (select top 1 1 from @Repair group 
63by object, subobject, GenProcIDorigin having count(*) > 1 ) begin delete  @Repair from @Repair qr , (select min(uid_dialogdbqueue) as uid_dialogdbqueue
64 , object, subobject, GenProcIDOrigin from @Repair group by object, subobject, GenProcIDorigin having count(*) > 1 ) as x where qr.object = x.object and
65 qr.subobject = x.subobject and qr.GenProcIDorigin = x.GenProcIDorigin and qr.uid_dialogdbqueue <> x.uid_dialogdbqueue end  end     if @CountParameter 
66= 0 begin delete  @Repair from @Repair qr, (select min(uid_dialogdbqueue) as uid_dialogdbqueue , count(*) as CountItems from @Repair ) as x where x.CountItems
67 > @compresslimit and qr.uid_dialogdbqueue <> x.uid_dialogdbqueue end if @CountParameter = 1 begin delete  @Repair from @Repair qr, (select min(uid_dialogdbqueue
68) as uid_dialogdbqueue , object from @Repair group by object having count(*) > @compresslimit ) as x where qr.object = x.object and qr.uid_dialogdbqueue
69 <> x.uid_dialogdbqueue end if @CountParameter = 2 begin delete  @Repair from @Repair qr, (select min(uid_dialogdbqueue) as uid_dialogdbqueue , object,
70 subobject from @Repair group by object, subobject having count(*) > @compresslimit ) as x where qr.object = x.object and qr.subobject = x.subobject and
71 qr.uid_dialogdbqueue <> x.uid_dialogdbqueue end        update @Repair set Xtouched = ''  if @CountParameter = 0 begin if 1 = ( select count(*) from @Repair
72 ) begin update @Repair set XTouched = 'D' insert into QBMDBQueueCurrent(UID_DialogDBQueue , UID_Parameter, uid_SubParameter , GenProcID, SlotNumber, UID_Task
73, Generation, StartedAt) select newid(), '', '' , case when isnull(r.GenProcIDOrigin,'') = '' then @GenProcIDFallBack else r.GenProcIDOrigin end , @SlotNumber
74, @UID_Task, r.Generation, GETUTCDATE() from @Repair r where xtouched = 'D' option (maxdop 1) end end if @CountParameter = 1 begin update @Repair set XTouched
75 = 'D' where UID_DialogDBQueue in (select min(r.UID_DialogDBQueue) as UID_DialogDBQueue from @Repair r group by r.object having count(*) = 1 ) option (maxdop
76 1) insert into QBMDBQueueCurrent(UID_DialogDBQueue , UID_Parameter, uid_SubParameter , GenProcID, SlotNumber, UID_Task, Generation, StartedAt) select 
77newid(), r.object, '' , case when isnull(r.GenProcIDOrigin,'') = '' then @GenProcIDFallBack else r.GenProcIDOrigin end , @SlotNumber, @UID_Task, r.Generation
78, GETUTCDATE() from @Repair r where xtouched = 'D' option (maxdop 1) end if @CountParameter = 2 begin update @Repair set XTouched = 'D' where UID_DialogDBQueue
79 in (select min(r.UID_DialogDBQueue) as UID_DialogDBQueue from @Repair r group by r.object, r.subobject having count(*) = 1 ) option (maxdop 1) insert 
80into QBMDBQueueCurrent(UID_DialogDBQueue , UID_Parameter, uid_SubParameter , GenProcID, SlotNumber, UID_Task, Generation, StartedAt ) select newid(), r.object