dbo.QBM_PJobUpdateState_Bulk

1  create   procedure QBM_PJobUpdateState_Bulk (@JobsToChange QBM_YJobStateInfo readonly ) as begin  declare @JobQueueShadow QBM_YJobQueueShadow 
2declare @JobCountTotal int declare @JobIndexCurrent int = 1 declare @IsSendMail bit declare @errmsg nvarchar(1024) declare @Trees QBM_YSingleGUID declare
3 @XUserSpecial nvarchar(64) = 'QBM_PJobUpdateState' declare @XDateSpecial datetime = getutcdate() declare @writeHistory nvarchar(16) declare @Common_Jobservice_RedoDelayMinutes
4 int  declare @CountOfPostCMD int  declare @PostCMD nvarchar(max) declare @PostCMDBodyTree nvarchar(max) = concat(' 
5	updat','e dialogprocessStep 
6		set ProcessState = @neu
7		where uid_tree = @uid_tree
8			and isnull(ProcessState, '''') = @alt  
9	select @CountLines += @@Rowcount '
10 , char(13) , char(10) ) declare @PostCMDBodyJob nvarchar(max) = concat(' 
11	updat','e dialogprocessStep 
12		set ProcessState = @neu
13		where uid_Job = @UID_Job
14			and isnull(ProcessState, '''') <> @neu   
15	select @CountLines += @@Rowcount '
16 , char(13) , char(10) ) declare @StartLenPostCMD int declare @SQLcmd nvarchar(1024)  declare @PostCmdCollection QBM_YCursorBuffer   declare @PostCmdElementIndex
17 int declare @PostCmdElementCount int declare @FolgeJobs QBM_YDBQueueRaw  select @IsSendMail = 0 if dbo.QBM_FGIConfigparmValue('Common\MailNotification'
18) > ' ' begin select @IsSendMail = 1 end select @writeHistory = left(dbo.QBM_FGIConfigparmValue('Common\ProcessState\JobHistory'), 16)    select @Common_Jobservice_RedoDelayMinutes
19 = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('Common\Jobservice\RedoDelayMinutes'), 2)  declare @UID_Job varchar(38) declare @NewState nvarchar
20(32) declare @SuccesInfo int declare @MessageString nvarchar(max) declare @UID_NextJob varchar(38) declare @UID_Tree varchar(38) declare @UID_NOTnextJob
21 varchar(38) declare @isSplitOnly bit declare @UID_JobError varchar(38) declare @ProcessTrackingForJob bit declare @ProcessTrackingForTree bit declare 
22@ErrorNotify bit declare @SuccessNotify bit declare @MailAddress nvarchar(255) declare @MailSender nvarchar(255) declare @MailSubject nvarchar(255) declare
23 @MailBody nvarchar(1024) declare @MailAddress_success nvarchar(255) declare @MailSender_success nvarchar(255) declare @MailSubject_success nvarchar(255
24) declare @MailBody_success nvarchar(1024) declare @UID_JobsameServer varchar(38) declare @Retries int declare @DeferOnError bit declare @MinutesToDefer
25 int declare @Queue nvarchar(255) declare @JobChainName nvarchar(255) declare @GenProcID varchar(38) declare @IsToFreezeOnError bit declare @ComponentClass
26 nvarchar(255) declare @TaskName nvarchar(255) declare @UID_JobOrigin varchar(38)  declare @ready2exeOld varchar(16) declare @IsForHistory bit declare 
27@IsLastJobOfTree bit declare @IsToFreeze bit declare @IsAlreadyHistory bit declare @WasError bit declare @ServerName nvarchar(64) declare @ResetCounter
28 int  declare @IsLastJobOfSubtree bit declare @DebugLevel varchar(1) = 'W' SET XACT_ABORT OFF BEGIN TRY insert into @Trees (UID_SingleGuid) select distinct
29 q.UID_Tree from JobQueue q with (nolock)  where q.UID_Job in (select ch.UID_Job from @JobsToChange ch )   insert into @JobQueueShadow(UID_JobSameServer
30, Ready2EXE, StartAt, XDateUpdated, XUserUpdated, Retries, WasError, IsToFreezeOnError , ErrorMessages  , UID_JobError , UID_JobSuccess, GenProcID, XUserInserted
31 , XDateInserted, JobChainName , UID_Job, UID_Tree , ProcessTracking, ErrorNotify, NotifyAddress, NotifySubject, NotifyBody, NotifySender, SuccessNotify
32 , NotifyAddressSuccess, NotifySubjectSuccess, NotifyBodySuccess, NotifySenderSuccess , DeferOnError, MinutesToDefer, UID_JobOrigin, ComponentClass, TaskName
33  , Queue, IsForHistory , IsSplitOnly ) select distinct q.UID_JobSameServer, q.Ready2EXE, q.StartAt, q.XDateUpdated, q.XUserUpdated, q.Retries, q.WasError
34, q.IsToFreezeOnError ,case when jc.MessageString > ' ' then q.ErrorMessages  else null end  , q.UID_JobError , q.UID_JobSuccess, q.GenProcID, q.XUserInserted
35 , q.XDateInserted, q.JobChainName , q.UID_Job, q.UID_Tree , q.ProcessTracking, q.ErrorNotify, q.NotifyAddress, q.NotifySubject, q.NotifyBody, q.NotifySender
36, q.SuccessNotify , q.NotifyAddressSuccess, q.NotifySubjectSuccess, q.NotifyBodySuccess, q.NotifySenderSuccess , q.DeferOnError, q.MinutesToDefer, q.UID_JobOrigin
37, ComponentClass, q.TaskName  , q.Queue, q.IsForHistory , IsSplitOnly from JobQueue q with (nolock) left outer join @JobsToChange jc on q.UID_Job = jc.UID_Job
38 where q.UID_Tree in (select t.UID_SingleGuid from @Trees t ) select @JobCountTotal = count(*) from @JobsToChange while @JobIndexCurrent <= @JobCountTotal
39 begin select @PostCMD = 'declare @uid_Tree varchar(38) , @alt varchar(1), @neu varchar(1), @UID_Job varchar(38), @CountLines int = 0 ' + char(13) + char
40(10) select @StartLenPostCMD = len(@PostCMD) select top 1 @UID_Job = ch.UID_Job , @NewState = ch.NewState , @SuccesInfo = ch.SuccesInfo , @MessageString
41 = ch.MessageString from @JobsToChange ch where ch.SortOrder = @JobIndexCurrent   if right(rtrim(@UID_Job),2) = 'RA' begin goto ZyklusEnde end  if @SuccesInfo
42 in (1, -1)  begin select top 1 @UID_NextJob = isnull(sh.UID_JobSuccess,'') , @UID_NOTnextJob = case when isnull(sh.UID_JobError,'') = isnull(sh.UID_JobSuccess
43,'') then '' else isnull(sh.UID_JobError,'') end, @uid_tree = sh.UID_Tree , @isSplitOnly = 1,  @UID_JobError = sh.UID_JobError, @ProcessTrackingForJob 
44= isnull(sh.ProcessTracking,0), @errorNotify = isnull(sh.ErrorNotify,0), @successNotify = isnull(sh.SuccessNotify,0), @UID_JobsameServer = sh.UID_JobSameServer
45,  @Retries = isnull(sh.Retries,0), @DeferOnError = isnull(sh.DeferOnError,0), @MinutesToDefer = isnull(sh.MinutesToDefer,1), @MailAddress = sh.NotifyAddress
46, @MailSender = sh.NotifySender, @MailSubject = sh.NotifySubject, @MailBody = sh.NotifyBody, @MailAddress_success = sh.NotifyAddressSuccess, @MailSender_success
47 = sh.NotifySenderSuccess, @MailSubject_success = sh.NotifySubjectSuccess, @MailBody_success = sh.NotifyBodySuccess, @JobChainName = isnull(sh.JobChainName
48,''), @Queue = sh.Queue, @GenProcID = sh.GenProcID, @IsToFreezeOnError = 0,  @ComponentClass = sh.ComponentClass, @TaskName = sh.TaskName  , @UID_JobOrigin
49 = sh.UID_JobOrigin         , @ready2exeOld = sh.Ready2EXE , @IsForHistory = sh.IsForHistory , @IsLastJobOfSubtree = case when sh.UID_JobSameServer <> 
50ISNULL(ne.UID_JobSameServer,'') and @NewState in (N'FINISHED') then 1 else 0 end , @IsLastJobOfTree = case when isnull(sh.UID_JobSuccess, '') = '' and 
51@NewState in (N'FINISHED') then 1 else 0 end from @JobQueueShadow sh left outer join @JobQueueShadow ne on sh.UID_JobSuccess = ne.UID_Job where sh.UID_Job
52 = @UID_Job end else begin select top 1 @UID_NextJob = isnull(sh.UID_JobError,''), @UID_NOTnextJob = case when isnull(sh.UID_JobError,'') = isnull(sh.UID_JobSuccess
53,'') then '' else isnull(sh.UID_JobSuccess,'') end, @uid_tree = sh.UID_Tree , @isSplitOnly = isnull(sh.IsSplitOnly,0),  @UID_JobError = sh.UID_JobError
54, @ProcessTrackingForJob = isnull(sh.ProcessTracking,0), @errorNotify = isnull(sh.ErrorNotify,0), @successNotify = isnull(sh.SuccessNotify,0), @UID_JobsameServer
55 = sh.UID_JobSameServer,  @Retries = isnull(sh.Retries,0), @DeferOnError = isnull(sh.DeferOnError,0), @MinutesToDefer = isnull(sh.MinutesToDefer,1), @MailAddress
56 = sh.NotifyAddress, @MailSender = sh.NotifySender, @MailSubject = sh.NotifySubject, @MailBody = sh.NotifyBody, @MailAddress_success = sh.NotifyAddressSuccess
57, @MailSender_success = sh.NotifySenderSuccess, @MailSubject_success = sh.NotifySubjectSuccess, @MailBody_success = sh.NotifyBodySuccess, @JobChainName
58 = isnull(sh.JobChainName,''), @Queue = sh.Queue, @GenProcID = sh.GenProcID, @IsToFreezeOnError = isnull(sh.IsToFreezeOnError,0),  @ComponentClass = sh.ComponentClass
59, @TaskName = sh.TaskName  , @UID_JobOrigin = sh.UID_JobOrigin         , @ready2exeOld = sh.Ready2EXE , @IsForHistory = sh.IsForHistory , @IsLastJobOfSubtree
60 = case when sh.UID_JobSameServer <> ISNULL(ne.UID_JobSameServer,'') and @NewState in (N'FINISHED') then 1 else 0 end , @IsLastJobOfTree = case when isnull
61(sh.UID_JobError, '') = '' and @NewState in (N'FINISHED') then 1 else 0 end from @JobQueueShadow sh left outer join @JobQueueShadow ne on sh.UID_JobError
62 = ne.UID_Job where sh.UID_Job = @UID_Job end if @@rowcount = 0 begin select @errmsg = concat('#LDS#The process step to update is missing. Tree: {0} missing process step: {1}.|'
63 + 'not exists' , '|' , @UID_Job , '|' ) exec QBM_PJournal @errmsg, @@procid, 'W', @DebugLevel end select @ProcessTrackingForTree = @ProcessTrackingForJob
64  if @IsLastJobOfTree = 1 begin   if @ProcessTrackingForTree = 0 begin if exists (select top 1 1 from @JobQueueShadow s where s.UID_Tree = @UID_Tree and
65 s.ProcessTracking = 1 ) begin select @ProcessTrackingForTree = 1 end end if @ComponentClass = N'VI.JobService.JobComponents.DelayComponent' and @TaskName
66 = N'ReActivateJob'  and @WasError = 0 begin select @IsLastJobOfTree = 0 end end   if @newState = N'MISSING' begin update @JobQueueShadow set Ready2EXE
67 = N'MISSING'  , IsToFreezeOnError = case  when @isSplitOnly = 1 then q.IsToFreezeOnError when @UID_JobError > ' ' then q.IsToFreezeOnError else 1 end 
68, IsRecordModified = 1 , IsRecordModifiedExtended = 1 , XDateUpdated = @XDateSpecial , XUserUpdated = @XUserSpecial from @JobQueueShadow q where q.UID_Job
69 = @UID_Job and q.Ready2EXE in (N'LOADED', N'PROCESSING')  and not exists (select top 1 1 from @JobQueueShadow q3 where q3.Ready2EXE in( N'DELETE', N'HISTORY'
70) and q3.UID_Tree = @UID_Tree ) update @JobQueueShadow set Ready2EXE = N'FINISHED' , IsRecordModified = 1 , XDateUpdated = @XDateSpecial , XUserUpdated
71 = @XUserSpecial from @JobQueueShadow q where q.UID_Job = @UID_Job and q.Ready2EXE in (N'LOADED', N'PROCESSING')  and exists (select top 1 1 from @JobQueueShadow
72 q3 where q3.Ready2EXE in( N'DELETE', N'HISTORY') and q3.UID_Tree = @UID_Tree ) goto ZyklusEnde end    if @ready2exeOld not in (N'LOADED', N'PROCESSING'
73, N'MISSING')   and not ( @ComponentClass = N'VI.JobService.JobComponents.DelayComponent' and @TaskName = N'ReActivateJob' ) begin select @errmsg = concat
74('#LDS#The process step to update is in wrong state. Tree: {0} process step: {1} state {2}.|' , @uid_tree , '|' , @UID_Job , '|' , @ready2exeOld , '|' 
75) exec QBM_PJournal @errmsg, @@procid, 'W', @DebugLevel goto ZyklusEnde end   if @SuccesInfo in (1, -1) and @ComponentClass = N'VI.JobService.JobComponents.DelayComponent'
76 and @TaskName = N'ReActivateJob' begin    if exists (select top 1 1 from @JobQueueShadow q where q.UID_Job = @UID_Job and q.Ready2EXE in (N'FALSE', N'FINISHED'
77) ) begin goto ZyklusEnde end end  if isnull(@GenProcID,'') = '' begin select @errmsg = concat('#LDS#Process step generated without GenProcID {0}.|' , 
78@uid_tree , N'|' )   exec QBM_PJournal @errmsg, @@procid, 'W', @DebugLevel end            if @SuccesInfo in ( 0, 2) and @IsToFreezeOnError = 1 and ((@deferOnError
79 = 1 and @retries = 0) or @deferOnError = 0 ) begin select @IsToFreeze = 1 end else begin select @IsToFreeze = 0 end if @newstate = 'FINISHED'  begin  
80 if  ( @SuccesInfo = 0  and @deferOnError = 1  and @retries > 0 )  or @SuccesInfo = 2  begin   update @JobQueueShadow set retries = retries - case @SuccesInfo