dbo.QBM_PJobCreate

1 create   procedure QBM_PJobCreate (@ComponentClass nvarchar(1024) , @TaskName nvarchar(256) , @Parameters QBM_YParameterList readOnly     , @GenProcID
2 varchar(38) , @ObjectKeysAffected QBM_YParameterList readOnly  , @isToFreezeOnError bit = 0 , @Retries int = 1 , @priority int = 5 , @Jobchainname nvarchar
3(256) = '' , @StartAt datetime = null , @BasisObjectKey varchar(138) = null , @XUser nvarchar(64) = null , @QueueName nvarchar(256) = null  , @checkForExisting
4 bit = 0 ) as begin declare @DeferOnError bit = 0 declare @MinutesToDefer int = 0 declare @UID_Job varchar(38) = newid() declare @ParametersIntern QBM_YParameterList
5 declare @ExistingToReplace QBM_YParameterList declare @ObjectKeysAffectedNetto QBM_YParameterList declare @ObjectKeysAffected_intern QBM_YParameterList
6 declare @ReplaceRunner int declare @ReplaceWhereClause nvarchar(max) declare @DebugSwitch int = 0 declare @SQLTagMaster varchar(38) = 'QBM-ST-Is07' declare
7 @SQLTagAddOn varchar(38)= 'QBM-ST-SQLProcessingEnabled' declare @isToFreezeOnErrorConfig bit = 0 declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext
8('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser') declare @RelevantServer QBM_YParameterList    declare @CountRelevantServer int 
9= 0 declare @TempQueueAvailable bit = 0 declare @SimilarJobExists int = 0  declare @zerlegt QBM_YParameterList Declare @WhereClauseAdditional nvarchar(max
10) SET XACT_ABORT OFF BEGIN TRY if @DebugSwitch > 0 begin print 'entry create job ' + isnull(@Jobchainname, '<no @Jobchainname>') end if object_id('tempdb..#JobQueue'
11) is not null and object_id('tempdb..#QBMElementAffectedByJob') is not null begin select @TempQueueAvailable = 1 end select @isToFreezeOnErrorConfig = 
12case when dbo.QBM_FCVStringToInt( dbo.QBM_FGIConfigparmValue('Common\DBJobCreateWithFreeze'), 0) = 1 then 1 else @isToFreezeOnError end if @BasisObjectKey
13 > ' ' begin insert into @ObjectKeysAffected_intern (Parameter1) select a.Parameter1 from @ObjectKeysAffected a where a.Parameter1 > ' ' union select @BasisObjectKey
14 end else begin insert into @ObjectKeysAffected_intern (Parameter1) select a.Parameter1 from @ObjectKeysAffected a where a.Parameter1 > ' ' end if isnull
15(@Jobchainname, '') = '' begin select @Jobchainname = dbo.QBM_FGIJobCreateName (null) end if @Retries > 0 begin select @DeferOnError = 1 select @MinutesToDefer
16 = 2 end if @StartAt is null begin select @StartAt = GETUTCDATE() end if @XUser is null begin select @XUser = OBJECT_NAME(@@procid) end exec QBM_PSessionContextSet
17 'GenProcID', @GenProcID exec QBM_PSessionContextSet 'XUser', @XUser if @QueueName is null begin   if exists (select top 1 1 from QBMServerHasServerTag
18 sht with (readpast) where sht.UID_QBMServerTag = @SQLTagAddOn ) begin    insert into @RelevantServer(Parameter1, ContentShort, Parameter2) select y.UID_QBMServer
19, s.QueueName, dbo.QBM_FCVStringToGUID('', s.QueueName) from ( select x.UID_QBMServer from QBMServerHasServerTag x with (readpast) join QBMServer s with
20 (readpast) on x.UID_QBMServer = s.UID_QBMServer where x.UID_QBMServerTag in( @SQLTagAddOn) and s.NotUsedForJobCreation = 0  and s.IsInSoftwareUpdate =
21 0 and s.IsQBMServiceInstalled = 1 and s.QueueName > ' '  group by x.UID_QBMServer ) as y join QBMServer s with (readpast) on y.UID_QBMServer = s.UID_QBMServer
22 select @CountRelevantServer = @@ROWCOUNT   if @CountRelevantServer = 0 begin select top 1 @QueueName = s.QueueName from QBMServer s with (readpast) join
23 QBMServerHasServerTag sht with (readpast) on s.UID_QBMServer = sht.UID_QBMServer where sht.UID_QBMServerTag = @SQLTagMaster and s.QueueName > ' ' and 
24s.NotUsedForJobCreation = 0 end if @CountRelevantServer = 1 begin select top 1 @QueueName = sht.ContentShort from @RelevantServer sht end  if @CountRelevantServer
25 > 1 begin select top 1 @QueueName = sht.ContentShort from @RelevantServer sht left outer join QBMJobqueueOverview o with (readpast) on sht.Parameter2 
26= o.UID_QBMJobqueueOverview order by o.CountTrue + o.CountLoaded + o.CountProcessing + o.CountFalse asc end  end else begin  select top 1 @QueueName = 
27s.QueueName from QBMServer s with (readpast) join QBMServerHasServerTag sht with (readpast) on s.UID_QBMServer = sht.UID_QBMServer where sht.UID_QBMServerTag
28 = @SQLTagMaster and s.QueueName > ' ' and s.NotUsedForJobCreation = 0 end end insert into @ParametersIntern(Parameter1, Parameter2, Parameter3, ContentFull
29) select Parameter1, Parameter2, Parameter3, ContentFull from @Parameters union all select db.ParameterName, null, null, db.ParameterValue from dbo.QBM_FTJobCreateDBParameter
30(@ComponentClass, @TaskName, @Parameters) db  if @checkForExisting = 1 begin    select top 1 @SimilarJobExists = 1 from JobQueue q with (readpast) join
31 QBMElementAffectedByJob e with (readpast) on q.UID_Job = e.UID_Job join @ObjectKeysAffected_intern a on e.ObjectKeyAffected = a.Parameter1 where q.ComponentClass
32 = @ComponentClass and q.TaskName = @TaskName   and q.IsRootJob = 1 and q.Ready2EXE in( N'TRUE', N'FROZEN' ) and q.JobChainName = @Jobchainname if @TempQueueAvailable
33 = 1 and @SimilarJobExists = 0  begin select top 1 @SimilarJobExists += 1 from #JobQueue q with (nolock) join #QBMElementAffectedByJob e with (nolock) 
34on q.UID_Job = e.UID_Job join @ObjectKeysAffected_intern a on e.ObjectKeyAffected = a.Parameter1 where q.ComponentClass = @ComponentClass and q.TaskName
35 = @TaskName     and q.JobChainName = @Jobchainname end if @SimilarJobExists > 0 begin if @TempQueueAvailable = 0  begin insert into @ExistingToReplace
36(Parameter1) select distinct a.Parameter1 from @ObjectKeysAffected_intern a join QBMElementAffectedByJob e with (readpast) on e.ObjectKeyAffected = a.Parameter1
37 join JobQueue q with (readpast) on q.UID_Job = e.UID_Job where q.ComponentClass = @ComponentClass and q.TaskName = @TaskName and q.IsRootJob = 1 and q.Ready2EXE
38 in( N'TRUE', N'FROZEN' ) and q.JobChainName = @Jobchainname select @ReplaceRunner = @@ROWCOUNT end  else begin  insert into @ExistingToReplace(Parameter1
39) select a.Parameter1 from @ObjectKeysAffected_intern a join QBMElementAffectedByJob e with (readpast) on e.ObjectKeyAffected = a.Parameter1 join JobQueue
40 q with (readpast) on q.UID_Job = e.UID_Job where q.ComponentClass = @ComponentClass and q.TaskName = @TaskName and q.IsRootJob = 1 and q.Ready2EXE in(
41 N'TRUE', N'FROZEN' ) and q.JobChainName = @Jobchainname union select a.Parameter1 from @ObjectKeysAffected_intern a join #QBMElementAffectedByJob e with
42 (nolock) on e.ObjectKeyAffected = a.Parameter1 join #JobQueue q with (nolock) on q.UID_Job = e.UID_Job where q.ComponentClass = @ComponentClass and q.TaskName
43 = @TaskName   and q.JobChainName = @Jobchainname select @ReplaceRunner = @@ROWCOUNT end     if @ReplaceRunner > 0 begin if @DebugSwitch > 0 begin print
44 ' --- start replacing' end   select top 1 @ReplaceWhereClause = i.ContentFull from @ParametersIntern i where i.Parameter1 = 'whereclause' if @ReplaceWhereClause
45 like 'XObjectKey in (%' or @ReplaceWhereClause like '(XObjectKey in (%'  begin              insert into @zerlegt (Parameter1) select substring(s.value
46, 2, len(s.value) -2) as Parameter1 from string_split(case when @ReplaceWhereClause like 'XObjectKey in (%' then substring( @ReplaceWhereClause, 16, len
47(@ReplaceWhereClause) -16) when @ReplaceWhereClause like '(XObjectKey in (%' then substring( @ReplaceWhereClause, 17, len(@ReplaceWhereClause) -18) else
48 '' end ,',' ) s  delete @zerlegt from @zerlegt z join @ExistingToReplace i on z.Parameter1 = i.Parameter1   if exists (select top 1 1 from @zerlegt ) 
49begin  select @ReplaceWhereClause = concat( 'XObjectKey in ' , dbo.QBM_FCVParameter1AsOneInClause(@zerlegt) )  end else begin   goto endLabel end update
50 @ParametersIntern set ContentFull = @ReplaceWhereClause where Parameter1 = 'whereclause' end  end  end  if @DebugSwitch > 0 begin print '@ExistingToReplace'
51 select r.Parameter1 from @ExistingToReplace r end if @DebugSwitch > 0 begin print 'nach replace' select * from @ParametersIntern end  if @BasisObjectKey
52 > ' ' begin insert into @ObjectKeysAffectedNetto(Parameter1) select a.Parameter1 from @ObjectKeysAffected_intern a left outer join @ExistingToReplace 
53r on a.Parameter1 = r.Parameter1 where r.Parameter1 is null union select @BasisObjectKey end else begin insert into @ObjectKeysAffectedNetto(Parameter1
54) select a.Parameter1 from @ObjectKeysAffected_intern a left outer join @ExistingToReplace r on a.Parameter1 = r.Parameter1 where r.Parameter1 is null 
55end end else begin  if @BasisObjectKey > ' ' begin insert into @ObjectKeysAffectedNetto(Parameter1) select a.Parameter1 from @ObjectKeysAffected_intern
56 a union select @BasisObjectKey end else begin insert into @ObjectKeysAffectedNetto(Parameter1) select a.Parameter1 from @ObjectKeysAffected_intern a end
57 end   if exists (select top 1 1 from @ParametersIntern i where i.Parameter1 = 'WhereClauseAdditional' ) begin select top 1 @WhereClauseAdditional = i.ContentFull
58 from @ParametersIntern i where i.Parameter1 = 'WhereClauseAdditional' update @ParametersIntern set ContentFull = case when ISNULL(@WhereClauseAdditional
59, '') = '' then i.ContentFull  else concat( CONVERT(nvarchar(max), '(') , i.ContentFull  , ') and (' , @WhereClauseAdditional , ')' ) end from @ParametersIntern
60 i where i.Parameter1 = 'WhereClause' delete @ParametersIntern from @ParametersIntern i where i.Parameter1 = 'WhereClauseAdditional' end  if @TempQueueAvailable
61 = 0  begin insert into jobqueue ( ComponentAssembly, ComponentClass, DeferOnError, ErrorNotify , ExecutionType, IgnoreErrors, IsSplitOnly, JobChainName
62, MaxInstance, MinutesToDefer, Retries, ParamIN, Priority, Queue, Ready2EXE, StartAt, TaskName, UID_Job, UID_JobError, UID_JobSameServer, UID_JobSuccess
63, UID_Tree, WasError, XDateInserted, XDateUpdated, XUserInserted, XUserUpdated, IsRootJob, GenProcID , isToFreezeOnError, BasisObjectKey) select c.ComponentAssembly
64, c.ComponentClass, @DeferOnError , 0 , t.ExecutionType, 0, 0, @Jobchainname, t.MaxInstance, @MinutesToDefer , @Retries , dbo.QBM_FCVListToJobParameter
65(@ComponentClass, @TaskName, @ParametersIntern ) , @priority, @QueueName, N'TRUE', @startat, @TaskName, @uid_job, N'', @uid_job, N'', newid(), 0, GetUTCDate
66(), GetUTCDate(), @XUser, @XUser, 1, @GenProcID, @isToFreezeOnErrorConfig, @BasisObjectKey from JobComponent c with (readpast) join JobTask t with (readpast
67) on c.UID_JobComponent = t.UID_JobComponent where c.ComponentClass = @ComponentClass and t.TaskName = @TaskName insert into QBMElementAffectedByJob(UID_Job
68, ObjectKeyAffected) select @uid_job, e.Parameter1 from @ObjectKeysAffectedNetto e end else begin insert into #JobQueue ( ComponentAssembly, ComponentClass
69, DeferOnError, ErrorNotify , ExecutionType, IgnoreErrors, IsSplitOnly, JobChainName, MaxInstance, MinutesToDefer, Retries, ParamIN, Priority, Queue, Ready2EXE
70, StartAt, TaskName, UID_Job, UID_JobError, UID_JobSameServer, UID_JobSuccess, UID_Tree, WasError, XDateInserted, XDateUpdated, XUserInserted, XUserUpdated
71, IsRootJob, GenProcID , isToFreezeOnError, BasisObjectKey) select c.ComponentAssembly, c.ComponentClass, @DeferOnError , 0 , t.ExecutionType, 0, 0, @Jobchainname
72, t.MaxInstance, @MinutesToDefer , @Retries , dbo.QBM_FCVListToJobParameter(@ComponentClass, @TaskName, @ParametersIntern ) , @priority, @QueueName, N'TRUE'
73, @startat, @TaskName, @uid_job, N'', @uid_job, N'', newid(), 0, GetUTCDate(), GetUTCDate(), @XUser, @XUser, 1, @GenProcID, @isToFreezeOnErrorConfig, @BasisObjectKey
74 from JobComponent c with (readpast) join JobTask t with (readpast) on c.UID_JobComponent = t.UID_JobComponent where c.ComponentClass = @ComponentClass
75 and t.TaskName = @TaskName insert into #QBMElementAffectedByJob(UID_Job, ObjectKeyAffected) select @uid_job, e.Parameter1 from @ObjectKeysAffectedNetto
76 e end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18
77, 1) WITH NOWAIT END CATCH endLabel: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R exec QBM_PSessionContextSet 'XUser', @XUser_R return end 
78