dbo.QBM_PJobQueueInit_i

1   create   procedure QBM_PJobQueueInit_i (@queue nvarchar(255), @SessionID varchar(38) = ''   , @SetSessionOnly bit   ) as begin  declare @uid_tree
2 varchar(38)  declare @uid_job varchar(38)  declare @SQLcmd nvarchar(max) declare @isMasterSQL int = 0 declare @UID_QBMServer varchar(38) declare @SessionID_DB
3 varchar(38) declare @msg nvarchar(1000) declare @GenProcID varchar(38) declare @XUser nvarchar(64) declare @Xdate datetime = getutcdate() declare @UID_QBMJobqueueOverview
4 varchar(38) = dbo.QBM_FCVStringToGUID('', @queue) declare @QueueMirror QBM_YCursorBuffer  declare @ElementCount int declare @ElementIndex int declare 
5@AffectedQueuesExceptMe QBM_YParameterlist declare @JobqueueMessage nvarchar(1000) declare @NewState nvarchar(16)  declare @SQLOverviewUpdate nvarchar(max
6) declare @OverviewUpdateRowcount int declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext
7('XUser') SET XACT_ABORT OFF BEGIN TRY select @GenProcID = dbo.QBM_FGISessionContext('') select @XUser = dbo.QBM_FGISessionContext('XUser')  if isnull(@XUser
8, '') = '' begin select @XUser = left(Object_name(@@procid), 64) exec QBM_PSessionContextSet 'GenProcID', @GenProcID exec QBM_PSessionContextSet 'XUser'
9, @XUser end   select top 1 @UID_QBMServer = UID_QBMServer , @SessionID_DB = isnull(SessionID, '') from QBMServer where QueueName = @queue if exists (select
10 top 1 1 from QBMServerHasServerTag ht where ht.UID_QBMServer = @UID_QBMServer and ht.UID_QBMServerTag = 'QBM-ST-Is07' ) begin select @isMasterSQL = 1 
11end if isnull(@UID_QBMServer, '') = '' begin  if dbo.QBM_FGIConfigparmValue('Common\Jobservice\AutoCreateServerFromQueues') > ' ' begin select @UID_QBMServer
12 = NEWID()  select @SessionID_DB = @SessionID insert into QBMServer (QueueName, UID_QBMServer, XObjectKey , Ident_Server , XDateInserted, XDateUpdated,
13 XUserInserted, XUserUpdated, SessionID  ) select @queue, @UID_QBMServer, dbo.QBM_FCVElementToObjectKey1('QBMServer', 'UID_QBMServer', @UID_QBMServer) 
14, case charindex('\', reverse(@queue)) when 0 then @queue else reverse(SUBSTRING(reverse(@queue), 1, charindex('\', reverse(@queue))-1 )) end as Ident_Server
15 , GETUTCDATE(), GETUTCDATE(), @XUser, @XUser, @SessionID_DB end else begin select @msg = '#LDS#No server found for the queue {0}.|' + @queue + N'|' raiserror
16(@msg, 18, 2) with nowait end end if isnull(@SessionID_DB, '') <> isnull(@SessionID, '') and @SessionID > ' ' begin exec QBM_PSessionContextSet 'GenProcID'
17, @GenProcID exec QBM_PSessionContextSet 'XUser', @XUser update QBMServer set SessionID = @SessionID  , XDateUpdated = @Xdate , XUserUpdated = @XUser where
18 UID_QBMServer = @UID_QBMServer end if @SetSessionOnly = 1 begin goto endWithoutReset end    select @SQLOverviewUpdate = concat('
19update QBMJobqueueOverview 
20	set IsInitQueueRunning = 1
21	from QBMJobqueueOverview o 
22	where o.UID_QBMJobqueueOverview = '''
23, @UID_QBMJobqueueOverview, '''
24	 and o.IsInitQueueRunning = 0' ) exec @OverviewUpdateRowcount = QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLOverviewUpdate
25 , @LockTimeout_ms = 20 , @MaxWaitTimeForLock_s = 3 , @ProcIDForJournal = default , @HandleErrorSilent = 0 if @OverviewUpdateRowcount = 0 begin goto endWithoutReset
26 end      if  (select top 1 DataOrigin from DialogDatabase d with (readpast) where d.IsMainDatabase = 1 )  <> dbo.QBM_FGIDBDataOrigin() begin select @JobqueueMessage
27 = N'status set to FROZEN by reinitializing jobqueue because of wrong DialogDatabase.DataOrigin' select @NewState = 'FROZEN' end else begin select @JobqueueMessage
28 = N'status changed by reinitializing jobqueue' select @NewState = 'TRUE' end  exec QBM_PSessionContextSet 'GenProcID', @GenProcID exec QBM_PSessionContextSet
29 'XUser', @XUser update jobqueue set ready2exe = @NewState , UID_JobSameServer = q.UID_Job , ErrorMessages = dbo.QBM_FGIJobQueueMessageAdd(ErrorMessages
30, @JobqueueMessage)  , XDateUpdated = @Xdate , XUserUpdated = @XUser from JobQueue q join (select UID_Tree, count(*) as CountItems from JobQueue where 
31queue = @queue and Ready2EXE in (N'LOADED', N'PROCESSING', N'MISSING') group by UID_Tree having count(*) = 1 ) as x on q.UID_Tree = x.UID_Tree where ready2exe
32 in (N'LOADED', N'PROCESSING', N'MISSING')  and not exists (select top 1 1 from JobQueue q3 where q3.Ready2EXE in( N'DELETE', N'HISTORY') and q3.UID_Tree
33 = q.UID_Tree )  update JobQueue set Ready2EXE = N'HISTORY' , ErrorMessages = dbo.QBM_FGIJobQueueMessageAdd(ErrorMessages, N'status set to HISTORY by reinitializing jobqueue because of undefined root job'
34 )  , XDateUpdated = @Xdate , XUserUpdated = @XUser from JobQueue q join (select q2.UID_Tree  from JobQueue q2 group by q2.UID_Tree having sum(convert(int
35, q2.IsRootJob)) <> 1 ) as x on q.UID_Tree = x.UID_Tree where q.Ready2EXE not in (N'HISTORY', N'DELETE')   insert into @QueueMirror (UID1 , UID2 ) select
36 distinct q.uid_tree, dbo.QBM_FGIJobQueueTopJob(q.UID_Tree) from jobqueue q where q.queue = @queue and ( q.ready2exe in (N'LOADED', N'PROCESSING', N'MISSING'
37, N'TRUE')  or exists (select top 1 1 from ( select uid_tree , count(z.ready2exe) as CountItems from jobqueue q1 left outer join ( values (N'TRUE') , (
38N'LOADED') , (N'PROCESSING') ) as z (Ready2exe) on z.ready2exe = q1.ready2exe group by uid_tree having count(z.ready2exe) = 0 ) as y where y.uid_tree =
39 q.uid_tree ) ) and not exists (select top 1 1 from jobqueue q3 where q3.ready2exe in( N'FROZEN', N'OVERLIMIT'  , N'DELETE', N'HISTORY'  ) and q3.uid_tree
40 = q.uid_tree )  and exists (select top 1 1 from jobqueue q4 where q4.uid_tree = q.uid_tree group by q4.uid_tree having count(*) > 1 ) insert into @AffectedQueuesExceptMe
41(Parameter1) select distinct dbo.QBM_FCVStringToGUID('', q.Queue) from JobQueue q with (nolock) where q.UID_Tree in (select UID_Tree from @QueueMirror 
42) and q.Queue <> @queue    exec QBM_PSessionContextSet 'GenProcID', @GenProcID exec QBM_PSessionContextSet 'XUser', @XUser  if exists (select top 1 1 from
43 @QueueMirror m where m.UID2 = ''  ) begin  update jobqueue set Ready2EXE = N'HISTORY'  , XDateUpdated = @Xdate , XUserUpdated = @XUser from JobQueue q
44 join (select m.UID1 as UID_Tree from @QueueMirror m join JobQueue j on m.UID1  = j.UID_Tree where m.UID2 = '' group by m.UID1  having count(*) = sum (case
45 when j.Ready2EXE = N'FINISHED' then 1 else 0 end ) ) f on q.UID_Tree = f.UID_Tree end  delete  @QueueMirror where UID2 = ''  update jobqueue set ready2exe
46 = N'FALSE' , ErrorMessages = dbo.QBM_FGIJobQueueMessageAdd(ErrorMessages, @JobqueueMessage)  , XDateUpdated = @Xdate , XUserUpdated = @XUser from Jobqueue
47 q join @QueueMirror m on q.uid_tree = m.UID1  and q.uid_Job <> m.UID2   and q.ready2exe not in ( N'FINISHED', N'DELETE', N'HISTORY'  , N'FALSE' ) exec
48 QBM_PSessionContextSet 'GenProcID', @GenProcID exec QBM_PSessionContextSet 'XUser', @XUser update jobqueue set ready2exe = @NewState , ErrorMessages =
49 dbo.QBM_FGIJobQueueMessageAdd(ErrorMessages, @JobqueueMessage)  , XDateUpdated = @Xdate , XUserUpdated = @XUser from jobqueue q join @QueueMirror m on
50 q.uid_tree = m.UID1  and q.uid_Job = m.UID2   where q.Ready2EXE <> @NewState   exec QBM_PSessionContextSet 'GenProcID', @GenProcID exec QBM_PSessionContextSet
51 'XUser', @XUser select @ElementCount = COUNT(*) from @QueueMirror select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @uid_tree
52 = bu.UID1 , @uid_job = bu.UID2 from @QueueMirror bu where bu.ElementIndex = @ElementIndex exec QBM_PJobQueueSetSameServer @uid_job select @ElementIndex
53 += 1 end  if @UID_QBMServer > ' ' begin exec QBM_PQBMServerHasJobService @UID_QBMServer end   select @SQLOverviewUpdate = concat('
54 update QBMJobqueueOverview
55	set IsInvalid = 1
56	from QBMJobqueueOverview o 
57	where o.IsInvalid = 0
58	and o.UID_QBMJobqueueOverview = '''
59, @UID_QBMJobqueueOverview, '''
60' ) exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLOverviewUpdate , @LockTimeout_ms = 20 , @MaxWaitTimeForLock_s
61 = 5 , @ProcIDForJournal = default , @HandleErrorSilent = 0 set lock_timeout 500 begin Try update QBMJobqueueOverview set IsInvalid = 1 from QBMJobqueueOverview
62 o join @AffectedQueuesExceptMe a on o.UID_QBMJobqueueOverview = a.Parameter1 where o.IsInvalid = 0 and o.IsInitQueueRunning = 0 end Try begin catch  set
63 lock_timeout -1 end catch set lock_timeout -1 END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow
64() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH  ende: select @SQLOverviewUpdate = concat('
65update QBMJobqueueOverview
66	set IsInitQueueRunning = 0
67	from QBMJobqueueOverview o 
68	where o.UID_QBMJobqueueOverview = '''
69, @UID_QBMJobqueueOverview, '''
70	 and o.IsInitQueueRunning = 1
71' ) exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLOverviewUpdate , @LockTimeout_ms
72 = 20 , @MaxWaitTimeForLock_s = 5 , @ProcIDForJournal = default , @HandleErrorSilent = 0 endWithoutReset: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R
73 exec QBM_PSessionContextSet 'XUser', @XUser_R return end 
74