dbo.QBM_PJobQueueLoad

1    create   procedure QBM_PJobQueueLoad (@queue nvarchar(200), @Sekunden int, @priority int, @SoftwareRevisionOnServer int = 0, @CurrentReady nvarchar
2(max) = N''  , @SessionID varchar(38) = ''  ) as begin declare @IsQueueDisabled bit declare @IsQueueSuspended bit  declare @perf2 QBM_YJobQueueTasks declare
3 @jobs QBM_YSingleGUID  declare @LastTimeoutCheck datetime declare @CountItems int declare @uid_self varchar(38) declare @uid_jobToCheck varchar(38) declare
4 @InsertCheckVersion bit  declare @uid_QBMServer varchar(38) declare @XObjectKeyQBMServer varchar(138) declare @UID_ParentQBMServer varchar(38) declare
5 @IsMasterSQL bit = 0 declare @IsNoAutoupdate bit declare @PhysicalServerName nvarchar(64) declare @IsInSoftwareUpdate bit declare @QueueWhereClause nvarchar
6(255)  declare @SoftwareRevision int  declare @uid_jobReact varchar(38) declare @ComponentClass nvarchar(255) declare @TaskName nvarchar(64) declare @countDeliver
7 int  declare @AnzahlTotal int declare @LimitTotal int declare @LoadedJobsTimeOut int declare @ExistsMissing bit declare @JustNow datetime = getutcdate
8()  declare @ready QBM_YJobQueueTasks declare @jobqueue QBM_YJobQueueContent declare @SessionID_DB varchar(38) declare @msg nvarchar(1000) declare @IsQBMServiceInstalled
9 bit declare @GenProcID varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser nvarchar(64) = dbo.QBM_FGISessionContext('XUser') declare @DebugSwitch
10 int = 0 declare @DebugLevel varchar(1) = 'I' declare @XUserSpecialString nvarchar(64) = 'QBM_PJobQueueLoad' declare @RaiseCondition nvarchar(1000) = ''
11 declare @SQLTagMaster varchar(38) = 'QBM-ST-Is07' declare @QueueName nvarchar(256) declare @ConfigparmAutoupdate int declare @ElementLast int declare 
12@ElementBufferMulti QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @SQLLastJobFetchTime nvarchar(max) declare @RCAppLockRequest
13 int declare @MyName nvarchar(64) = object_name(@@procid)  declare @IsExclusiveContained bit = 0 declare @SimilarQueueExists bit = 0 declare @BinInWiederholung
14 bit = 0 declare @CountLoadedJobs_float float = 0.0 declare @CountDeletedJobs_float float = 0.0 declare @LimitForRedoExclusive float = 0.6  declare @MaxRunInWorstCase
15 int = 15  declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser') SET XACT_ABORT
16 OFF BEGIN TRY begin transaction   if isnull(@XUser, '') = '' begin select @XUser = left(Object_name(@@procid), 64) exec QBM_PSessionContextSet 'GenProcID'
17, @GenProcID exec QBM_PSessionContextSet 'XUser', @XUser end set nocount on  select top 1 @IsInSoftwareUpdate = isnull(IsInSoftwareUpdate, 0), @uid_QBMServer
18 = rtrim(uid_QBMServer), @IsNoAutoupdate = isnull(IsNoAutoupdate,0), @UID_ParentQBMServer = isnull(UID_ParentQBMServer,'') , @PhysicalServerName = isnull
19(PhysicalServerName, '')  , @IsQueueDisabled = isnull(IsJobServiceDisabled, 0) , @IsQueueSuspended = isnull(IsJobServiceSuspended, 0) , @LastTimeoutCheck
20 = isnull(LastTimeoutCheck, '1899-12-30') , @SessionID_DB = isnull(SessionID, '') , @IsQBMServiceInstalled = isnull(IsQBMServiceInstalled, 0) , @XObjectKeyQBMServer
21 = s.XObjectKey from QBMServer s with (readpast ) where QueueName = @queue  select @SQLLastJobFetchTime = concat('
22	updat','e QBMServer
23		set LastJobFetchTime = '''
24 , dbo.QBM_FCVDatetimeToString( @JustNow ) , '''
25		where UID_QBMServer = ''' , @uid_QBMServer , '''
26	') select @ConfigparmAutoupdate = dbo.QBM_FCVStringToInt
27(dbo.QBM_FGIConfigparmValue('Common\Autoupdate'), 0) insert into @ready ( ComponentClass, TaskName, CountReadyOrDeliver ) select left(parametername, charindex
28('|', parametername) -1 ), substring(parametername, charindex('|', parametername) +1 , 255 ) , convert(int, N'0' + parametervalue) from dbo.QBM_FCVStringToListSplitted
29(@CurrentReady, nchar(7), 0, 0 , N'=')  if exists (select top 1 1 from DialogDatabase with (readpast ) where ismaindatabase = 1 and ( IsJobServiceDisabled
30 = 1 or UpdatePhase in (3,4)  ) ) begin select @RaiseCondition = '#LDS#Processing is disabled (queues globally switched off).|' goto FehlerWerfen end  
31if dbo.QBM_FGISingleUserRunning () = 1 begin select @RaiseCondition = '#LDS#Processing is disabled (single user mode running).|' goto FehlerWerfen end 
32  if  exists (select top 1 1 from DialogDatabase d with (Readpast) where d.UID_CutOffTask = 'QBM-K-CommonWaitForCompiler' ) begin select @RaiseCondition
33 = '#LDS#Processing is disabled (wait for compiler).|' goto JournalEintragen end if exists (select top 1 1 from DialogScriptAssembly a with (readpast) 
34where a.IsValid = 0 ) begin select @RaiseCondition = '#LDS#Processing is disabled (wait for compiler).|' goto JournalEintragen end if dbo.QBM_FGIDBOwner
35() = 'CCC' and @ConfigparmAutoupdate = 1 and exists (select top 1 1 from QBMFileRevision r with (readpast ) where ( r.UID_QBMFileRevision = 'QBM-D004E74592BB32809960DB77764C1EBE'
36 or r.FileName = 'VI.DB.dll'  ) and r.FileContent is null ) begin select @RaiseCondition = '#LDS#Processing is disabled (wait for filled QBMFileRevision).|'
37 goto JournalEintragen end  if exists (select top 1 1 from sys.triggers t with (nolock ) join sys.tables ta with (nolock ) on t.parent_id = ta.object_id
38 join DialogTable dt with (readpast ) on ta.name = dt.TableName collate database_default where t.name like '___[_]%'   and t.is_disabled = 1 and (dbo.QBM_FGIModuleExists
39(left(t.name, 3)) = 1 or t.name like 'GEN[_]%' )  ) begin select @RaiseCondition = '#LDS#Processing is disabled (deactivated trigger(s)).|' goto JournalEintragen
40 end if exists (select top 1 1 from sys.foreign_keys c with (nolock ) join sys.objects t with (nolock ) on c.parent_object_id = t.object_id join DialogTable
41 dt with (readpast ) on t.name = dt.TableName collate database_default where c.is_disabled = 1 ) begin select @RaiseCondition = '#LDS#Processing is disabled (deactivated constraint(s)).|'
42 goto JournalEintragen end   select @InsertCheckVersion = 0 if @IsQueueDisabled = 1 or @IsQueueSuspended = 1 begin select @RaiseCondition = '#LDS#Processing is disabled (the queue "{0}" is switched off).|'
43 + @queue + '|' goto FehlerWerfen end if exists (select top 1 1 from QBMServerHasServerTag ht with (readpast ) where ht.UID_QBMServer = @UID_QBMServer 
44and ht.UID_QBMServerTag = 'QBM-ST-Is07' ) begin select @isMasterSQL = 1 end if @uid_QBMServer > ' '  and @SessionID > ' '  and isnull(@SessionID_DB, ''
45) = ''  begin exec QBM_PSessionContextSet 'GenProcID', @GenProcID exec QBM_PSessionContextSet 'XUser', @XUser update QBMServer set SessionID = @SessionID
46 , XDateUpdated = @JustNow , XUserUpdated = @XUser where uid_QBMServer = @uid_QBMServer select @SessionID_DB = @SessionID end  if @SessionID_DB <> isnull
47(@SessionID, '') begin select @RaiseCondition = '#LDS#Session ID for queue {0} does not match. DB: {1} Query:{2}.|' + @queue + N'|' + @SessionID_DB + N'|'
48 + isnull(@SessionID, N'') + N'|'  goto FehlerWerfen end      if @IsQueueDisabled = 1 or @IsQueueSuspended = 1 begin goto ende end select @QueueWhereClause
49 = 'XObjectKey in (''' + @XObjectKeyQBMServer + ''')' if @IsInSoftwareUpdate = 0 and exists (select top 1 1 from QBMServer js with (readpast ) where js.PhysicalServerName
50 = @PhysicalServerName and js.uid_QBMServer <> @uid_QBMServer and js.IsInSoftwareUpdate = 1 ) begin    if @isMasterSQL = 0 begin goto ende end end if @IsNoAutoupdate
51 = 0 and @ConfigparmAutoupdate = 1 begin  select top 1 @SoftwareRevision = ChangeCounter from DialogSemaphor with (readpast ) where ChangeContext = 'SOFTWAREREVISION'
52 if @SoftwareRevision is null begin select @SoftwareRevision = 0 end if (  @SoftwareRevision <> @SoftwareRevisionOnServer ) begin  if not exists (select
53 top 1 1 from JobQueue q with (nolock) left outer join QBMElementAffectedByJob e with (nolock) on q.UID_Job = e.UID_Job where q.TaskName = 'FireGenEvent'
54 and q.ComponentAssembly = 'HandleObjectComponent' and q.ParamIN like N'%ObjectType~9~QBMServer%' and q.ParamIN like N'%EventName~12~Checkversion%'  and
55 e.ObjectKeyAffected = @XObjectKeyQBMServer ) and not exists (select top 1 1 from JobQueue q with(nolock) left outer join QBMElementAffectedByJob e with
56 (nolock) on q.UID_Job = e.UID_Job where q.JobChainName in( 'vid_jobserverUpdateCheck', 'vid_jobserverUpdateReplace' )  and e.ObjectKeyAffected = @XObjectKeyQBMServer
57 ) begin  select @InsertCheckVersion = 1 end end   if @SoftwareRevision <> @SoftwareRevisionOnServer begin select @IsInSoftwareUpdate = 1 end else begin
58 select @IsInSoftwareUpdate = 0  end  if @insertCheckVersion = 1 begin  select top 1 @QueueName = s.QueueName from QBMServer s with (readpast ) join QBMServerHasServerTag
59 sht with (readpast ) on s.UID_QBMServer = sht.UID_QBMServer where sht.UID_QBMServerTag = @SQLTagMaster and s.QueueName > ' '  exec QBM_PJobCreate_HOFireEvent_B
60 'QBMServer', @QueueWhereClause, 'Checkversion', @GenProcID , @AdditionalObjectKeysAffected = DEFAULT , @QueueName = @QueueName , @checkForExisting = 1
61 end  exec QBM_PSessionContextSet 'GenProcID', @GenProcID exec QBM_PSessionContextSet 'XUser', @XUser update QBMServer set IsInSoftwareUpdate = @IsInSoftwareUpdate
62 , XDateUpdated = @JustNow , XUserUpdated = @XUser where uid_QBMServer = @uid_QBMServer and isnull(IsInSoftwareUpdate,0) <> @IsInSoftwareUpdate if @IsInSoftwareUpdate
63 = 1 begin goto EchteDaten end end  if @IsQBMServiceInstalled = 0 begin exec QBM_PQBMServerHasJobService @uid_QBMServer end    if not exists (      select
64 top 1 1 from QBMJobqueueOverview o with (readpast) where o.QueueName = @queue and ( o.CountTrue > 0 or o.CountLoaded > 0 or o.CountOverlimt > 0 or o.CountMissing
65 > 0 or o.CountProcessing > 0 or o.CountFalse > 0 or o.CountFrozen > 0 )  and o.IsInitQueueRunning = 0 ) begin goto ende end       NachOverlimit:   select
66 @LoadedJobsTimeOut = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('Common\Jobservice\LoadedJobsTimeOut'), 0)  if exists (      select top 1 1 from
67 QBMJobqueueOverview o with (readpast) where o.QueueName = @queue and o.CountMissing > 0 ) begin select @ExistsMissing = 1 end else begin select @ExistsMissing
68 = 0 end  if @ExistsMissing = 1 begin select @uid_self = right(rtrim(newid()) + 'RA',38) insert into @jobqueue ( UID_Job , UID_Tree , UID_JobSameServer
69 , TaskName, ComponentClass, ComponentAssembly, ExecutionType, ParamIN, StartAt, Retries, Priority, DeferOnError, MinutesToDefer, IgnoreErrors, MaxInstance
70, UID_JobError, UID_JobSuccess, IsSplitOnly) select @uid_self,  newid(),  newid(),  'InitQueue',  'VI.JobService.JobComponents.JobCheckComponent',  'JobService'
71,  'INTERNAL',   N'~' + dbo.QBM_FCVStringToJobParameter(N'uid_job') + dbo.QBM_FCVStringToJobParameter(newid())+ dbo.QBM_FCVStringToJobParameter(N'') +dbo.QBM_FCVStringToJobParameter
72(N'Queue') + dbo.QBM_FCVStringToJobParameter(@queue)+ dbo.QBM_FCVStringToJobParameter(N'') +dbo.QBM_FCVStringToJobParameter(N'uid_self') + dbo.QBM_FCVStringToJobParameter
73(@uid_self)+ dbo.QBM_FCVStringToJobParameter(N'') ,  @JustNow,  1, 15, 0, 0, 1,  1,  '' ,  '',  0  goto Ausliefern end   if datediff(mi, @LastTimeoutCheck
74 , @JustNow) > @LoadedJobsTimeOut begin  exec QBM_PSessionContextSet 'GenProcID', @GenProcID exec QBM_PSessionContextSet 'XUser', @XUser update QBMServer
75 set LastTimeoutCheck = @JustNow , XDateUpdated = @JustNow , XUserUpdated = @XUser where uid_QBMServer = @uid_QBMServer if exists (select top 1 1 from 
76QBMJobqueueOverview o with (readpast) where o.QueueName = @queue  and o.CountLoaded + o.CountProcessing > 0 ) begin if exists (select top 1 1 from jobqueue
77 q with (READPAST)  where ready2exe in (N'LOADED', N'PROCESSING') and queue = @queue and datediff(mi, xdateupdated, @JustNow) > @LoadedJobsTimeOut ) begin
78  if exists (select top 1 1 from jobqueue q with (READPAST)  where ready2exe in (N'LOADED', N'PROCESSING') and queue = @queue and datediff(mi, xdateupdated
79, @JustNow) > @LoadedJobsTimeOut and q.uid_job = dbo.QBM_FGIJobQueueTopJob(q.uid_tree)  and not exists (select top 1 1 from @ready p where p.ComponentClass
80 = q.componentclass and p.taskname = q.taskname ) ) begin goto Reaktivierung  end end end  end  goto EchteDaten Reaktivierung:  delete  @ElementBufferMulti