dbo.QBM_PDBQCS_SlotRunner

1   create   procedure QBM_PDBQCS_SlotRunner (@SlotNumber int  , @UID_Task varchar(38) , @ProcedureToCall varchar(30) , @IsWithoutTransaction bit
2   , @isBulkenabled int , @CountReallyProcessed int output   ) as begin declare @RunnerResult int = 2     declare @ErrorMessage nvarchar(4000) = ''  declare
3 @ErrorSource nvarchar(64) = ''  declare @object varchar(38) declare @subObject varchar(38) declare @GenProcID varchar(38)  declare @StartTime datetime
4 = getutcdate()  declare @TotalRuntime_ms float  declare @ProcedureToPrepare varchar(30) declare @ProcedureToCallMissig bit = 0 declare @ProcedureToPrepareExists
5 bit = 0  declare @DebugLevel char(1) = 'I'  declare @Set0Retries int = 10 declare @SlotNumberSource int declare @SlotNumberTarget int declare @DBQueueToMove
6 QBM_YDBQCSCurrentToMove  declare @DebugSwitch int = 0 declare @DebugMessage nvarchar(1000)   declare @ModuleInUse QBM_YParameterList declare @ElementCount
7 int declare @ElementIndex int declare @childtask varchar(38)  declare @RowCountBeforeReset int declare @RowCountAfterReset int SET XACT_ABORT OFF truncate
8 table #JobQueue truncate table #QBMElementAffectedByJob if @SlotNumber <= 997 begin  exec QBM_PTransactionLevelCheck 0, @@procid, 'entry' end if @DebugSwitch
9 > 0 begin print 'eintritt Runner  slot' + str(@slotnumber) + ' Task ' + @UID_Task end select @ProcedureToPrepare = concat(left(@ProcedureToCall, 4) , 
10'R' , substring(@ProcedureToCall, 6, 30) )  if exists (select top 1 1 from sys.objects o where o.type = 'P' and o.name = @ProcedureToPrepare ) begin select
11 @ProcedureToPrepareExists = 1 end  if @DebugSwitch > 0 begin select @DebugMessage = concat('runner @SlotNumber ' , str(@SlotNumber) , '  runner @UID_Task '
12 , isnull(@UID_Task, '<null>') , '  runner @ProcedureToCall ' , isnull(@ProcedureToCall, '<null>') ) exec QBM_PJournal @debugmessage, @@procid, 'T', @DebugLevel
13 end  if @ProcedureToPrepareExists = 1 begin select @RowCountBeforeReset = count(*) from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber
14 if @isBulkenabled = 1 begin BEGIN TRY set lock_timeout 2000  exec @ProcedureToPrepare @SlotNumber END TRY BEGIN CATCH select @ErrorSource = 'execute slot Prepare'
15 select @ErrorMessage = ERROR_MESSAGE() exec QBM_PSessionErrorAdd default set lock_timeout -1  goto Fehlerauswertung END CATCH set lock_timeout -1  end
16 else  begin   select top 1 @object = rtrim(isnull(qc.UID_Parameter, '')), @subobject = rtrim(isnull(qc.UID_SubParameter, '')), @GenProcID = rtrim(isnull
17(qc.GenProcID, '')) from QBMDBQueueCurrent qc with (readpast)  where qc.SlotNumber = @SlotNumber BEGIN TRY set lock_timeout 2000  exec @ProcedureToPrepare
18 @SlotNumber, @object, @subobject, @GenProcID END TRY BEGIN CATCH select @ErrorSource = 'execute slot Prepare' select @ErrorMessage = ERROR_MESSAGE() exec
19 QBM_PSessionErrorAdd default set lock_timeout -1  goto Fehlerauswertung END CATCH set lock_timeout -1  end  select @RowCountAfterReset = count(*) from
20 QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber   if @RowCountAfterReset = 0 begin select @RunnerResult = 0 goto ende end end  
21if @IsWithoutTransaction = 0 begin begin transaction end BEGIN TRY if @isBulkenabled = 1 begin BEGIN TRY   truncate table #QBMDBQueueTemp if @IsWithoutTransaction
22 = 0  begin exec QBM_PSessionContextSet 'TempQueue', 'True' end  set lock_timeout 2000  if not exists ( select top 1 1 from QBMDBQueueCurrent cu with (readpast
23) where cu.SlotNumber = @SlotNumber ) begin select @DebugMessage = CONCAT( OBJECT_NAME(@@procid) , ' empty DBQueueCurrent found (bulk) ' , ' UID_Task = '
24, case when ISNULL(@UID_Task, '') = '' then ' <empty> ' else @UID_Task end ) exec QBM_PJournal @DebugMessage, @@procid, 'D', @DebugLevel select @RunnerResult
25 = 1  goto Fehlerauswertung end exec @ProcedureToCall @SlotNumber  if dbo.QBM_FGISessionContext('TempQueue') > ' ' begin if exists ( select top 1 1 from
26 #QBMDBQueueTemp ) begin exec QBM_PTempQueueEnd end exec QBM_PSessionContextSet 'TempQueue', '' end  select @RunnerResult = 0 select @ErrorMessage = ''
27 END TRY BEGIN CATCH select @ErrorSource = 'execute slot bulk' select @ErrorMessage = ERROR_MESSAGE() exec QBM_PSessionErrorAdd default  END CATCH set 
28lock_timeout -1  exec QBM_PSessionContextSet 'TempQueue', '' end else begin                select top 1 @object = rtrim(isnull(qc.UID_Parameter, '')), 
29@subobject = rtrim(isnull(qc.UID_SubParameter, '')), @GenProcID = rtrim(isnull(qc.GenProcID, '')) from QBMDBQueueCurrent qc with (readpast)  where qc.SlotNumber
30 = @SlotNumber if @@ROWCOUNT = 0 or @GenProcID is null begin select @DebugMessage = CONCAT( OBJECT_NAME(@@procid) , ' empty values found ' , ' UID_Task = '
31, case when ISNULL(@UID_Task, '') = '' then ' <empty> ' else @UID_Task end , ' GenProcID = ' , case when ISNULL(@GenProcID, '') = '' then ' <empty> ' else
32 @GenProcID end , ' UID_Parameter = ' , case when ISNULL(@object, '') = '' then ' <empty> ' else @object end , ' UID_SubParameter = ' , case when ISNULL
33(@subObject, '') = '' then ' <empty> ' else @subObject end ) exec QBM_PJournal @DebugMessage, @@procid, 'D', @DebugLevel  select @RunnerResult = 1  goto
34 Fehlerauswertung end BEGIN TRY set lock_timeout 10000  exec @ProcedureToCall @SlotNumber, @object, @subobject, @GenProcID select @RunnerResult = 0 select
35 @ErrorMessage = '' END TRY BEGIN CATCH select @ErrorSource = 'execute slot single' select @ErrorMessage = ERROR_MESSAGE() exec QBM_PSessionErrorAdd default
36  END CATCH set lock_timeout -1  end  END TRY BEGIN CATCH  select @ErrorMessage = concat(ERROR_MESSAGE(), ' task: ' , @UID_Task) exec QBM_PSessionErrorAdd
37 default select @ErrorSource = 'main catch' exec QBM_PSessionContextSet 'TempQueue', '' END CATCH Fehlerauswertung: if @ErrorSource > ' ' begin if not 
38exists (select top 1 1 from sys.objects o where o.type = 'P' and o.name = @ProcedureToCall ) begin select @ProcedureToCallMissig = 1 end if dbo.QBM_FGISessionErrorIsDeadlock
39(default) = 1  or @ProcedureToCallMissig = 1 begin select @RunnerResult = 1     end else begin select @RunnerResult = -1 end end if @DebugSwitch > 0 begin
40 print 'Runnerresult ' + str(@RunnerResult) end if @RunnerResult <> 0 begin if @@trancount > 0 begin rollback transaction end end else begin if @IsWithoutTransaction
41 = 0 begin commit end end if @SlotNumber <= 997 begin exec QBM_PTransactionLevelCheck 0, @@procid, 'exit' end if @RunnerResult = 0 begin  if exists (select
42 top 1 1 from #JobQueue ) begin insert into JobQueue ( ComponentAssembly, ComponentClass, DeferOnError, ErrorNotify, ExecutionType, IgnoreErrors, IsSplitOnly
43, JobChainName, MaxInstance, MinutesToDefer, Retries, ParamIN, Priority , Queue, Ready2EXE, StartAt, TaskName, UID_Job, UID_JobError, UID_JobSameServer
44, UID_JobSuccess, UID_Tree, WasError, XDateInserted, XDateUpdated, XUserInserted, XUserUpdated, IsRootJob, GenProcID , isToFreezeOnError, BasisObjectKey
45 ) select ComponentAssembly, ComponentClass, DeferOnError, ErrorNotify, ExecutionType, IgnoreErrors, IsSplitOnly, JobChainName, MaxInstance, MinutesToDefer
46, Retries, ParamIN, Priority , Queue, Ready2EXE, StartAt, TaskName, UID_Job, UID_JobError, UID_JobSameServer, UID_JobSuccess, UID_Tree, WasError, XDateInserted
47, XDateUpdated, XUserInserted, XUserUpdated, IsRootJob, GenProcID , isToFreezeOnError, BasisObjectKey from #JobQueue end if exists (select top 1 1 from
48 #QBMElementAffectedByJob ) begin insert into QBMElementAffectedByJob (UID_Job, ObjectKeyAffected) select UID_Job, ObjectKeyAffected from #QBMElementAffectedByJob
49 end    if exists (select top 1 1 from QBMDBQueueTaskMetric t with (nolock) where t.UID_TaskParent = @UID_Task ) begin declare @DBQueueElements_01 QBM_YDBQueueRaw
50 insert into @DBQueueElements_01(Object, SubObject, GenProcID) select cu.UID_Parameter, cu.UID_SubParameter, max(cu.GenProcID) from QBMDBQueueCurrent cu
51 with (readpast) where cu.SlotNumber = @SlotNumber group by cu.UID_Parameter, cu.UID_SubParameter declare @FolgeTask QBM_YCursorBuffer  if exists (select
52 top 1 1 from sys.objects o where o.name = 'QBMVSystemOverview' and o.type = 'V' ) begin  insert into @ModuleInUse(Parameter1) select v.Value from QBMVSystemOverview
53 v    where v.Element = N'Module in use' insert into @FolgeTask (UID1) select t.UID_Task from QBMDBQueueTaskMetric t with (readpast) left outer join @ModuleInUse
54 v on v.Parameter1 = concat(left(t.UID_Task, 3) , ' ', 'no') where t.UID_TaskParent = @UID_Task and v.Parameter1 is null select @ElementCount = @@ROWCOUNT
55 end else begin insert into @FolgeTask (UID1) select t.UID_Task from QBMDBQueueTaskMetric t with (nolock) where t.UID_TaskParent = @UID_Task select @ElementCount
56 = @@ROWCOUNT end select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @Childtask = bu.UID1 from @FolgeTask bu where bu.ElementIndex
57 = @ElementIndex exec QBM_PDBQueueInsert_Bulk @childtask, @DBQueueElements_01 select @ElementIndex += 1 end  end    select @CountReallyProcessed = 0 while
58 @Set0Retries > 0 begin  BEGIN TRY  delete @DBQueueToMove  insert into @DBQueueToMove(UID_DialogDBQueue) select cul.UID_DialogDBQueue from QBMDBQueueCurrent
59 cul  where cul.SlotNumber = @SlotNumber if @@ROWCOUNT > 0 begin select @SlotNumberSource = @SlotNumber  select @SlotnumberTarget = 0 exec @CountReallyProcessed
60 = QBM_PDBQCS_CurrentMoveSlot @DBQueueToMove, @SlotNumberSource, @SlotnumberTarget end select @Set0Retries = 0 END TRY BEGIN CATCH select @DebugMessage
61 = concat(ERROR_MESSAGE() , '(' , 'update ', ' QBMDBQueueCurrent set SlotNumber = 0 where SlotNumber = ' , str(@Slotnumber) , ')' , ' set0Retry ' , str
62(@Set0Retries) )  exec QBM_PSessionErrorAdd default exec QBM_PWaitForSeconds 0.3 if @DebugMessage is null begin select @DebugMessage = '<empty message> from set 0 retry'
63 end exec QBM_PJournal @DebugMessage, @@PROCID, 'D', @DebugLevel select @Set0Retries -= 1 END CATCH end  goto Nachbehandlung end  select @ErrorMessage 
64= concat('(' , isnull(@ErrorSource, '<empty @ErrorSource>') , ')' , @ErrorMessage ) if @RunnerResult = -1  begin  select @ErrorMessage = concat(@ErrorMessage
65 , dbo.QBM_FGISessionErrorForLog() ) exec QBM_PJournal @ErrorMessage, @@procid, 'E', @DebugLevel  goto Nachbehandlung end if @RunnerResult = 1  begin select
66 @ErrorMessage = concat(isnull(@UID_Task, '<empty @UID_Task>') , ':' , @ErrorMessage ) exec QBM_PJournal @ErrorMessage, @@PROCID, 'D', @DebugLevel end 
67if @RunnerResult = 2  begin select @ErrorMessage = concat(@UID_Task , ':' , ' @RunnerResult = 2 ', @ErrorMessage) exec QBM_PJournal @ErrorMessage, @@PROCID
68, 'E', @DebugLevel  select @RunnerResult = 1 end goto ende   Nachbehandlung: if @SlotNumber > 997  begin goto ende end select @TotalRuntime_ms = DATEDIFF
69(MS, @StartTime, GETUTCDATE()) if @TotalRuntime_ms > 0.8 * dbo.QBM_FCVStringToFloat(dbo.QBM_FGIConfigparmValue('QBM\DBQueue\KeepAlive'), 20)  * 60.0 * 
701000.0 begin select @ErrorMessage = CONCAT( '#LDS#Extra long running task= {0}  runtime = {2} minutes.|' , @UID_Task, '|' , dbo.QBM_FCVFloatToString(@TotalRuntime_ms
71 / 1000.0 / 60.0), '|' ) exec QBM_PJournal @ErrorMessage, @@PROCID, 'W', @DebugLevel end else begin if @TotalRuntime_ms > 5.0  * dbo.QBM_FCVStringToFloat
72( dbo.QBM_FGIConfigparmValue('QBM\DBQueue\DefaultRuntime'), 20)  * 1000.0  begin select @ErrorMessage = CONCAT( '#LDS#Long running task= {0}  runtime = {2} seconds.|'
73 , @UID_Task, '|' , dbo.QBM_FCVFloatToString(@TotalRuntime_ms / 1000.0), '|' ) exec QBM_PJournal @ErrorMessage, @@PROCID, 'I', @DebugLevel end end  ende:
74 set lock_timeout -1  exec QBM_PSessionContextSet 'TempQueue', '' truncate table #QBMDBQueueTemp if @ProcedureToCallMissig = 1 and @UID_Task > ' ' begin
75 exec QBM_PJournal @ErrorMessage, @@ProcID, 'E', @Debuglevel, 10 end return (@RunnerResult) end 
76