dbo.QBM_PJobqueueoverViewFill

1   create   procedure QBM_PJobqueueoverViewFill (@FullSearch bit = 0  ) as begin declare @ret int = 0  declare @DebugSwitch int = 0 declare @CountRows
2 int declare @QBMJobqueueOverview QBM_YJobqueueOverview  declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64)
3 = dbo.QBM_FGISessionContext('XUser') declare @GenProcID varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser nvarchar(64) = dbo.QBM_FGISessionContext
4('XUser')  declare @QueuesToUpdate QBM_YCursorBuffer declare @ElementCount int , @ElementIndex int , @UID_JobQueue varchar(38) SET XACT_ABORT OFF BEGIN
5 TRY  if isnull(@XUser, '') = '' begin select @XUser = left(Object_name(@@procid), 64) exec QBM_PSessionContextSet 'GenProcID', @GenProcID exec QBM_PSessionContextSet
6 'XUser', @XUser end if @DebugSwitch > 0 begin print 'Ausgangslage ' select isinvalid, * from QBMJobqueueOverview end insert into @QBMJobqueueOverview(UID_QBMJobqueueOverview
7 , XObjectKey , QueueName , CountTrue , CountLoaded , CountOverlimt , CountMissing , CountDelete , CountHistory , CountProcessing , CountFalse , CountFrozen
8 , CountFinished ) select dbo.QBM_FCVStringToGUID('', y.QueueName) as UID_QBMJobqueueOverview , dbo.QBM_FCVElementToObjectKey1('QBMJobqueueOverview', 'UID_QBMJobqueueOverview'
9, dbo.QBM_FCVStringToGUID('', y.QueueName)) as XObjectKey , y.QueueName , y.CountTrue , y.CountLoaded , y.CountOverlimt , y.CountMissing , y.CountDelete
10 , y.CountHistory , y.CountProcessing , y.CountFalse , y.CountFrozen , y.CountFinished from ( select x.Queue as QueueName, SUM(x.CountTrue ) as CountTrue
11 , SUM(x.CountLoaded ) as CountLoaded , SUM(x.CountOverlimt ) as CountOverlimt , SUM(x.CountMissing ) as CountMissing , SUM(x.CountDelete ) as CountDelete
12 , SUM(x.CountHistory ) as CountHistory , SUM(x.CountProcessing ) as CountProcessing , SUM(x.CountFalse ) as CountFalse , SUM(x.CountFrozen ) as CountFrozen
13 , SUM(x.CountFinished ) as CountFinished from ( select q.Queue, case q.Ready2EXE when N'TRUE' then 1 else 0 end as CountTrue , case q.Ready2EXE when N'LOADED'
14 then 1 else 0 end as CountLoaded , case q.Ready2EXE when N'OVERLIMT' then 1 else 0 end as CountOverlimt , case q.Ready2EXE when N'MISSING' then 1 else
15 0 end as CountMissing , case q.Ready2EXE when N'DELETE' then 1 else 0 end as CountDelete , case q.Ready2EXE when N'HISTORY' then 1 else 0 end as CountHistory
16 , case q.Ready2EXE when N'PROCESSING' then 1 else 0 end as CountProcessing , case q.Ready2EXE when N'FALSE' then 1 else 0 end as CountFalse , case q.Ready2EXE
17 when N'FROZEN' then 1 else 0 end as CountFrozen , case q.Ready2EXE when N'FINISHED' then 1 else 0 end as CountFinished from JobQueue q with (readpast)
18 left outer join QBMJobqueueOverview o with (readpast) on q.Queue = o.QueueName where ISNULL(o.IsInvalid, 1 ) = 1 or @FullSearch = 1 ) as x group by x.Queue
19 ) as y select @CountRows = @@ROWCOUNT if @DebugSwitch > 0 begin print 'queues summiert ' + str(@CountRows) select * from @QBMJobqueueOverview end   BEGIN
20 TRY set lock_timeout 5  merge into QBMJobqueueOverview as t using @QBMJobqueueOverview as s on t.UID_QBMJobqueueOverview = s.UID_QBMJobqueueOverview when
21 matched then update set t.CountTrue = s.CountTrue , t.CountLoaded = s.CountLoaded , t.CountOverlimt = s.CountOverlimt , t.CountMissing = s.CountMissing
22 , t.CountDelete = s.CountDelete , t.CountHistory = s.CountHistory , t.CountProcessing = s.CountProcessing , t.CountFalse = s.CountFalse , t.CountFrozen
23 = s.CountFrozen , t.CountFinished = s.CountFinished , t.IsInvalid = 0 when not matched by target then insert (UID_QBMJobqueueOverview , XObjectKey , QueueName
24 , CountTrue , CountLoaded , CountOverlimt , CountMissing , CountDelete , CountHistory , CountProcessing , CountFalse , CountFrozen , CountFinished , IsInvalid
25 ) values (s.UID_QBMJobqueueOverview , s.XObjectKey , s.QueueName , s.CountTrue , s.CountLoaded , s.CountOverlimt , s.CountMissing , s.CountDelete , s.CountHistory
26 , s.CountProcessing , s.CountFalse , s.CountFrozen , s.CountFinished , 0 ) when not matched by source and ( t.IsInvalid = 1 or @FullSearch = 1) then update
27 set t.CountTrue = 0 , t.CountLoaded = 0 , t.CountOverlimt = 0 , t.CountMissing = 0 , t.CountDelete = 0 , t.CountHistory = 0 , t.CountProcessing = 0 , 
28t.CountFalse = 0 , t.CountFrozen = 0 , t.CountFinished = 0 , t.IsInvalid = 0 ; select @CountRows = @@ROWCOUNT END TRY BEGIN CATCH select @CountRows = 0
29 if @DebugSwitch > 0 begin print 'catch vom merge' end  insert into @QueuesToUpdate (UID1) select o.UID_QBMJobqueueOverview from @QBMJobqueueOverview o
30 select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 if @DebugSwitch > 0 begin print 'Zu ändern im Einzelschuß' + str(@ElementCount) end while @ElementIndex
31 <= @ElementCount begin select top 1 @UID_JobQueue = u.UID1 from @QueuesToUpdate u where u.ElementIndex = @ElementIndex if @DebugSwitch > 0 begin print
32 'einzelupdate für ' + @UID_JobQueue end set lock_timeout 5 BEGIN TRY update QBMJobqueueOverview set CountTrue = s.CountTrue , CountLoaded = s.CountLoaded
33 , CountOverlimt = s.CountOverlimt , CountMissing = s.CountMissing , CountDelete = s.CountDelete , CountHistory = s.CountHistory , CountProcessing = s.CountProcessing
34 , CountFalse = s.CountFalse , CountFrozen = s.CountFrozen , CountFinished = s.CountFinished , IsInvalid = 0 from QBMJobqueueOverview as t join @QBMJobqueueOverview
35 as s on t.UID_QBMJobqueueOverview = s.UID_QBMJobqueueOverview where t.UID_QBMJobqueueOverview = @UID_JobQueue select @CountRows += @@ROWCOUNT END TRY 
36BEGIN CATCH if @DebugSwitch > 0 begin print 'catch vom einzel update' end END CATCH set lock_timeout -1 select @ElementIndex += 1 end END CATCH if @DebugSwitch
37 > 0 begin print 'queues geändert ' + str(@CountRows) select isinvalid, * from QBMJobqueueOverview o with (nolock) end if @FullSearch = 1 begin if exists
38 (select top 1 1 from QBMJobqueueOverview o left outer join QBMServer d on o.UID_QBMJobqueueOverview = dbo.QBM_FCVStringToGUID('', d.QueueName) where d.UID_QBMServer
39 is null ) begin  set lock_timeout 5 BEGIN TRY delete QBMJobqueueOverview from QBMJobqueueOverview o left outer join QBMServer d on o.UID_QBMJobqueueOverview
40 = dbo.QBM_FCVStringToGUID('', d.QueueName) where d.UID_QBMServer is null END TRY BEGIN CATCH  if @DebugSwitch > 0 begin print 'catch vom egal, wir kommen wieder mal vorbei'
41 end END CATCH set lock_timeout -1 end end END TRY BEGIN CATCH declare @Message nvarchar(4000) = concat(N'#LDS#Error running QBM_PJobqueueoverViewFill with FullSearch = {0}.|'
42 , str(@FullSearch), '|' ) if @DebugSwitch > 0 begin print 'catch von gesamt' end exec QBM_PSessionErrorAdd default if dbo.QBM_FGISessionErrorIsDeadlock
43(default) = 1 or dbo.QBM_FGISessionErrorIsMissingCo(default) = 1 begin  select @ret = 0  end else begin RAISERROR (@Message, 18, 1) WITH NOWAIT end END
44 CATCH endLabel: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R exec QBM_PSessionContextSet 'XUser', @XUser_R return @ret end 
45