dbo.QBM_PDBQCS_SlotExec

1   create   procedure QBM_PDBQCS_SlotExec (@SlotNumber int , @UID_Task varchar(38)  , @CountParameter int  , @Procedurename varchar(30) , @IsWithoutTransaction
2 bit   , @ChangeLimit int , @isBulkenabled bit , @CountRowsProcessed int output   , @ChangedElements int output    , @OverLoadCount int output    , @InsertedSomethingInQueue
3 int output   ) as begin declare @Msg nvarchar(1000) = newid() declare @MyProcname nvarchar(256) = 'QBM_DBQueueProcess'  declare @GenProcID varchar(38)
4 declare @RunnerResult int    declare @ExecutionType int  = case when @SlotNumber < 997 then 0 else @SlotNumber end declare @CountToProcessAfterReplace
5 int , @CountReallyProcessed int = 0 declare @SQLCmd nvarchar(max) declare @DebugSwitch int = 0 declare @DebugLevel varchar(1) = 'I' declare @DebugMessage
6 nvarchar(1000) declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser') declare
7 @DeadlockPriority int = -5  declare @ErrorBuffer QBM_YSessionError declare @ChangeLimitString varchar(64)  SET XACT_ABORT OFF if @DebugLevel = 'T' begin
8 declare @TracePathlength int select top 1 @TracePathlength = m.PathLength from QBMDBQueueTaskMetric m where m.UID_Task = @UID_Task declare @TraceMessage
9 nvarchar(max) select @TraceMessage = concat('QBM_PDBQCS_SlotExec ', convert(char(36), @UID_Task) , ' Pathlength ', str(@TracePathlength) , ' Slot ' , 
10str(@SlotNumber) ) exec QBM_PJournal @TraceMessage, @@procid, 'T', @DebugLevel end BEGIN TRY exec QBM_PSessionContextSet 'INSERTEDINTODBQUEUE', '' exec
11 QBM_PTransactionLevelCheck 0, @@procid, 'Eintritt muß transaktionsfrei sein' exec QBM_PSessionErrorClean   set deadlock_priority @DeadlockPriority select
12 @SQLCmd = dbo.QBM_FSQConnectionSettings() exec sp_executeSQL @SQLCmd exec QBM_PSessionContextSet 'OVERLOADREACHED', '' exec QBM_PSessionContextSet 'CHANGELIMITREACHED'
13, '' exec QBM_PSessionContextSet 'CHANGELIMITTIMEFORDELTA', ''  set nocount on exec QBM_PSessionContextSet 'GenProcID', @GenProcID exec QBM_PSessionContextSet
14 'XUser', @MyProcname if @SlotNumber <= 997 begin  exec QBM_PTransactionLevelCheck 0, @@procid, 'point a' end  if object_id('tempdb..#QBMDeltaInsert') 
15is null begin CREATE TABLE #QBMDeltaInsert ( Element varchar(38) collate database_default not null , AssignedElement varchar(138) collate database_default
16 not null , XOrigin int DEFAULT 0 , XIsInEffect bit default 0 , NewPK varchar(38) collate database_default null , GenProcID varchar(38) collate database_default
17  , TargetTableView varchar(30) collate database_default , ElementColumnView varchar(30) collate database_default , AssignedElementColumnView varchar(30
18) collate database_default , index #QBMDeltaInsert1 (Element, AssignedElement, GenProcID ) , index #QBMDeltaInsert2 (GenProcID ) ) create index #QBMDeltaInsert3
19 on #QBMDeltaInsert(AssignedElement ) include (Element) end else begin truncate table #QBMDeltaInsert end if object_id('tempdb..#QBMDeltaDelete') is null
20 begin CREATE TABLE #QBMDeltaDelete ( Element varchar(38) collate database_default not null , AssignedElement varchar(138) collate database_default not
21 null , GenProcID varchar(38) collate database_default  , TargetTableView varchar(30) collate database_default , ElementColumnView varchar(30) collate 
22database_default , AssignedElementColumnView varchar(30) collate database_default , index #QBMDeltaDelete1 (Element, AssignedElement, GenProcID) , index
23 #QBMDeltaDelete2 (GenProcID) ) create index #QBMDeltaDelete3 on #QBMDeltaDelete(AssignedElement ) include (Element) end else begin truncate table #QBMDeltaDelete
24 end if object_id('tempdb..#QBMDeltaQuantity') is null begin CREATE TABLE #QBMDeltaQuantity ( Element  varchar(38) collate database_default not null , 
25GenProcID varchar(38) collate database_default PRIMARY KEY NONCLUSTERED (Element) ) end else begin truncate table #QBMDeltaQuantity end if object_id('tempdb..#QBMDeltaOrigin'
26) is null begin CREATE TABLE #QBMDeltaOrigin ( Element varchar(38) collate database_default not null , AssignedElement varchar(138) collate database_default
27 not null , GenProcID varchar(38) collate database_default , XOrigin int DEFAULT 0 , XIsInEffect bit default 0 , index #QBMDeltaOrigin1 (Element, AssignedElement
28, GenProcID) , index #QBMDeltaOrigin2 (GenProcID) ) create index #QBMDeltaOrigin3 on #QBMDeltaOrigin(AssignedElement ) include (Element) end else begin
29 truncate table #QBMDeltaOrigin end if object_id('tempdb..#QBMDBQueueTemp') is null begin   Create Table #QBMDBQueueTemp ( UID_Task varchar(38) collate
30 database_default NULL , Object varchar(38) collate database_default NULL , SubObject varchar(38) collate database_default NULL , GenProcID varchar(38)
31 collate database_default NOT NULL , index #QBMDBQueueTemp(UID_Task ) ) end else begin truncate table #QBMDBQueueTemp end if object_id('tempdb..#JobQueue'
32) is null begin  Create Table #JobQueue( UID_Job varchar(38) collate database_default NOT NULL , UID_JobSameServer varchar(38) collate database_default
33 NULL , UID_Tree varchar(38) collate database_default NOT NULL , Queue nvarchar(128) collate database_default NOT NULL , ComponentClass nvarchar(200) collate
34 database_default NULL , ExecutionType nvarchar(16) collate database_default NULL , ComponentAssembly nvarchar(255) collate database_default NULL , TaskName
35 nvarchar(64) collate database_default NOT NULL , ParamIN nvarchar(max) collate database_default NULL , Ready2EXE nvarchar(32) collate database_default
36 NOT NULL , StartAt datetime NULL , XDateInserted datetime NULL , XDateUpdated datetime NULL , XUserInserted nvarchar(64) collate database_default NULL
37 , XUserUpdated nvarchar(64) collate database_default NULL , Retries int default 0 NULL , Priority int default 0 NULL , DeferOnError bit default 0 NULL
38 , MinutesToDefer int default 0 NULL , UID_JobError varchar(38) collate database_default NULL , UID_JobSuccess varchar(38) collate database_default NULL
39 , IgnoreErrors bit default 0 NULL , MaxInstance int default 0 NULL , JobChainName nvarchar(255) collate database_default NOT NULL  , IsSplitOnly bit default
40 0 NULL , WasError bit default 0 NULL , ErrorNotify bit default 0 NULL          , IsRootJob bit default 0 NULL , GenProcID varchar(38) collate database_default
41 NOT NULL  , IsToFreezeOnError bit default 0 NULL   , BasisObjectKey varchar(138) collate database_default NULL      , primary key nonCLUSTERED (UID_Job
42) ) end else begin truncate table #JobQueue end if object_id('tempdb..#QBMElementAffectedByJob') is null begin Create Table #QBMElementAffectedByJob( UID_Job
43 varchar(38) collate database_default NOT NULL , ObjectKeyAffected varchar(138) collate database_default NOT NULL ) end else begin truncate table #QBMElementAffectedByJob
44 end  if @SlotNumber <= 997 begin  exec QBM_PTransactionLevelCheck 0, @@procid, 'point b' end exec QBM_PDBQueueReplGenProcID @UID_Task, @SlotNumber, @ExecutionType
45, @CountParameter select top 1 @CountToProcessAfterReplace = COUNT(*) from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber if @CountToProcessAfterReplace
46 = 0 begin if @DebugSwitch > 0 begin select @DebugMessage = CONCAT( OBJECT_NAME(@@procid) , ' empty DBQueueCurrent after QBM_PDBQueueReplGenProcID ' , 
47' UID_Task = ', case when ISNULL(@UID_Task, '') = '' then ' <empty> ' else @UID_Task end ) exec QBM_PJournal @DebugMessage, @@procid, 'D', @DebugLevel 
48end select @RunnerResult = 1  goto ArbeitGetan end     if @ExecutionType = 997 begin   if exists (select top 1 1 from DialogDatabase d where d.UID_CutOffTask
49 > ' ' ) begin goto ArbeitGetan end end  BEGIN TRY  exec QBM_PSessionContextSet 'GenProcID', @GenProcID exec QBM_PSessionContextSet 'XUser', @MyProcname
50  if @DebugSwitch > 0 begin print 'Aufruf runner , slot = ' + str(@SlotNumber) + 'task = ' + isnull(@uid_Task, '<null>') end  select @ChangeLimitString
51 = dbo.QBM_FCVIntToString(@ChangeLimit) exec QBM_PSessionContextSet 'CHANGELIMIT', @ChangeLimitString exec QBM_PSessionContextSet 'CHANGELIMITREACHED',
52 '' exec QBM_PSessionContextSet 'OVERLOADREACHED', '' exec @RunnerResult = QBM_PDBQCS_SlotRunner @SlotNumber = @SlotNumber  , @UID_Task = @UID_Task , @ProcedureToCall
53 = @Procedurename , @IsWithoutTransaction = @IsWithoutTransaction  , @isBulkenabled = @isBulkenabled , @CountReallyProcessed = @CountReallyProcessed output
54    select @CountRowsProcessed = @CountReallyProcessed  select @ChangedElements = case @CountRowsProcessed when 0 then 0  else dbo.QBM_FCVStringToInt(dbo.QBM_FGISessionContext
55('CHANGELIMITREACHED'), 0) end select @OverLoadCount = dbo.QBM_FCVStringToInt(dbo.QBM_FGISessionContext('OVERLOADREACHED'), 0) if @DebugSwitch > 0 begin
56 print 'nach  runner , @RunnerResult =  ' + str(@RunnerResult) end  END TRY BEGIN CATCH  delete @ErrorBuffer insert into @ErrorBuffer (ErrorMessage, ErrorSeverity
57, ErrorState, ErrorNumber , ProcedureName, ProcedureLine, MessageDate , GenProcID, RepeatCounter, IsReThrow, SourceCode) select concat('Task execution error, not caught by runner '
58 , @uid_Task , NCHAR(13) , NCHAR(10) , ERROR_MESSAGE()) ,18, 1, 50000 , Object_name(@@PROCID), ERROR_LINE(), GETUTCDATE() , null, 0, 0, null exec QBM_PSessionErrorAdd
59 @ErrorBuffer   exec QBM_PJournal 'SessionError', @@procid, 'W', @DebugLevel exec QBM_PSessionErrorClean END CATCH    declare @SlotNumberSource int declare
60 @SlotNumberTarget int declare @DBQueueToMove QBM_YDBQCSCurrentToMove declare @RowsMoved int ArbeitGetan:    if @RunnerResult = 1  begin  select @SlotNumberSource
61 = @SlotNumber select @SlotnumberTarget = dbo.QBM_FGIDBQueueSlotResetType('Error03') delete @DBQueueToMove  insert into @DBQueueToMove(UID_DialogDBQueue
62) select cul.UID_DialogDBQueue from QBMDBQueueCurrent cul with (readpast) where cul.SlotNumber = @SlotNumber exec @RowsMoved = QBM_PDBQCS_CurrentMoveSlot
63 @DBQueueToMove, @SlotNumberSource, @SlotnumberTarget               end  if @RunnerResult = -1  begin  select @SlotNumberSource = @SlotNumber select @SlotnumberTarget
64 = dbo.QBM_FGIDBQueueSlotResetType('Error04') delete @DBQueueToMove  insert into @DBQueueToMove(UID_DialogDBQueue) select cul.UID_DialogDBQueue from QBMDBQueueCurrent
65 cul with (readpast) where cul.SlotNumber = @SlotNumber exec @RowsMoved = QBM_PDBQCS_CurrentMoveSlot @DBQueueToMove, @SlotNumberSource, @SlotnumberTarget
66               end  if exists (select top 1 1 from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber ) begin  select @SlotNumberSource
67 = @SlotNumber select @SlotnumberTarget = dbo.QBM_FGIDBQueueSlotResetType('Garbage02') delete @DBQueueToMove  insert into @DBQueueToMove(UID_DialogDBQueue
68) select cul.UID_DialogDBQueue from QBMDBQueueCurrent cul with (readpast) where cul.SlotNumber = @SlotNumber exec @RowsMoved = QBM_PDBQCS_CurrentMoveSlot
69 @DBQueueToMove, @SlotNumberSource, @SlotnumberTarget               end exec QBM_PSessionErrorClean END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default
70 RAISERROR ('', 18, 1) WITH NOWAIT END CATCH ende:  endLabel:  truncate table #QBMDeltaInsert truncate table #QBMDeltaDelete truncate table #QBMDeltaQuantity
71 truncate table #QBMDeltaOrigin truncate table #QBMDBQueueTemp truncate table #JobQueue truncate table #QBMElementAffectedByJob select @InsertedSomethingInQueue
72 = dbo.QBM_FCVStringToInt(dbo.QBM_FGISessionContext( 'INSERTEDINTODBQUEUE'), 0)  exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R exec QBM_PSessionContextSet
73 'XUser', @XUser_R return end 
74