dbo.QBM_PJobUpdateState_Bulk

1CREATE PROCEDURE QBM_PJobUpdateState_Bulk(2  @JobsToChange QBM_YJobStateInfo READONLY3)4AS5BEGIN6  DECLARE @JobQueueShadow QBM_YJobQueueShadow7  DECLARE @JobCountTotal int8  DECLARE @JobIndexCurrent int = 19  DECLARE @IsSendMail BIT10  DECLARE @errmsg nvarchar(1024)11  DECLARE @Trees QBM_YSingleGUID12  DECLARE @XUserSpecial nvarchar(64) = 'QBM_PJobUpdateState'13  DECLARE @XDateSpecial datetime = getutcdate()14  DECLARE @writeHistory nvarchar(16)15  DECLARE @Common_Jobservice_RedoDelayMinutes int16  DECLARE @CountOfPostCMD int17  DECLARE @PostCMD nvarchar(max)18  DECLARE @PostCMDBodyTree nvarchar(max) = CONCAT(' 19	updat',20  'e dialogprocessStep 21		set ProcessState = @neu22		where uid_tree = @uid_tree23			and isnull(ProcessState, '''') = @alt  24	select @CountLines += @@Rowcount '25  ,26  char(13),27  char(10))28  DECLARE @PostCMDBodyJob nvarchar(max) = CONCAT(' 29	updat',30  'e dialogprocessStep 31		set ProcessState = @neu32		where uid_Job = @UID_Job33			and isnull(ProcessState, '''') <> @neu   34	select @CountLines += @@Rowcount '35  ,36  char(13),37  char(10))38  DECLARE @StartLenPostCMD int39  DECLARE @SQLcmd nvarchar(1024)40  DECLARE @PostCmdCollection QBM_YCursorBuffer41  DECLARE @PostCmdElementIndex int42  DECLARE @PostCmdElementCount int43  DECLARE @FolgeJobs QBM_YDBQueueRaw44  SELECT @IsSendMail = 045  IF dbo.QBM_FGIConfigparmValue('Common\MailNotification') > ' '46  BEGIN47    SELECT @IsSendMail = 148  END49  SELECT @writeHistory =50  LEFT(dbo.QBM_FGIConfigparmValue('Common\ProcessState\JobHistory'),51  16)52  SELECT53    @Common_Jobservice_RedoDelayMinutes = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('Common\Jobservice\RedoDelayMinutes'),54    2)55  DECLARE @UID_Job varchar(38)56  DECLARE @NewState nvarchar(32)57  DECLARE @SuccesInfo int58  DECLARE @MessageString nvarchar(max)59  DECLARE @UID_NextJob varchar(38)60  DECLARE @UID_Tree varchar(38)61  DECLARE @UID_NOTnextJob varchar(38)62  DECLARE @isSplitOnly BIT63  DECLARE @UID_JobError varchar(38)64  DECLARE @ProcessTrackingForJob BIT65  DECLARE @ProcessTrackingForTree BIT66  DECLARE @ErrorNotify BIT67  DECLARE @SuccessNotify BIT68  DECLARE @MailAddress nvarchar(255)69  DECLARE @MailSender nvarchar(255)70  DECLARE @MailSubject nvarchar(255)71  DECLARE @MailBody nvarchar(1024)72  DECLARE @MailAddress_success nvarchar(255)73  DECLARE @MailSender_success nvarchar(255)74  DECLARE @MailSubject_success nvarchar(255)75  DECLARE @MailBody_success nvarchar(1024)76  DECLARE @UID_JobsameServer varchar(38)77  DECLARE @Retries int78  DECLARE @DeferOnError BIT79  DECLARE @MinutesToDefer int80  DECLARE @Queue nvarchar(255)81  DECLARE @JobChainName nvarchar(255)82  DECLARE @GenProcID varchar(38)83  DECLARE @IsToFreezeOnError BIT84  DECLARE @ComponentClass nvarchar(255)85  DECLARE @TaskName nvarchar(255)86  DECLARE @UID_JobOrigin varchar(38)87  DECLARE @ready2exeOld varchar(16)88  DECLARE @IsForHistory BIT89  DECLARE @IsLastJobOfTree BIT90  DECLARE @IsToFreeze BIT91  DECLARE @IsAlreadyHistory BIT92  DECLARE @WasError BIT93  DECLARE @ServerName nvarchar(64)94  DECLARE @ResetCounter int95  DECLARE @IsLastJobOfSubtree BIT96  DECLARE @DebugLevel varchar(1) = 'W'97  SET XACT_ABORT OFF98  BEGIN TRY99    INSERT INTO @Trees(UID_SingleGuid)100    SELECT101      DISTINCT q.UID_Tree102    FROM JobQueue q103      WITH(nolock)104    WHERE105      q.UID_Job IN(106    SELECT ch.UID_Job107    FROM @JobsToChange ch)108    INSERT INTO @JobQueueShadow(UID_JobSameServer,109    Ready2EXE,110    StartAt,111    XDateUpdated,112    XUserUpdated,113    Retries,114    WasError,115    IsToFreezeOnError,116    ErrorMessages,117    UID_JobError,118    UID_JobSuccess,119    GenProcID,120    XUserInserted,121    XDateInserted,122    JobChainName,123    UID_Job,124    UID_Tree,125    ProcessTracking,126    ErrorNotify,127    NotifyAddress,128    NotifySubject,129    NotifyBody,130    NotifySender,131    SuccessNotify,132    NotifyAddressSuccess,133    NotifySubjectSuccess,134    NotifyBodySuccess,135    NotifySenderSuccess,136    DeferOnError,137    MinutesToDefer,138    UID_JobOrigin,139    ComponentClass,140    TaskName,141    Queue,142    IsForHistory,143    IsSplitOnly)144    SELECT145      DISTINCT q.UID_JobSameServer,146      q.Ready2EXE,147      q.StartAt,148      q.XDateUpdated,149      q.XUserUpdated,150      q.Retries,151      q.WasError,152      q.IsToFreezeOnError,153    CASE154      WHEN jc.MessageString > ' ' THEN155    q.ErrorMessages156    ELSE NULL157    END,158    q.UID_JobError,159    q.UID_JobSuccess,160    q.GenProcID,161    q.XUserInserted,162    q.XDateInserted,163    q.JobChainName,164    q.UID_Job,165    q.UID_Tree,166    q.ProcessTracking,167    q.ErrorNotify,168    q.NotifyAddress,169    q.NotifySubject,170    q.NotifyBody,171    q.NotifySender,172    q.SuccessNotify,173    q.NotifyAddressSuccess,174    q.NotifySubjectSuccess,175    q.NotifyBodySuccess,176    q.NotifySenderSuccess,177    q.DeferOnError,178    q.MinutesToDefer,179    q.UID_JobOrigin,180    ComponentClass,181    q.TaskName,182    q.Queue,183    q.IsForHistory,184    IsSplitOnly185    FROM JobQueue q186      WITH(nolock)187    LEFT188    OUTER189    JOIN @JobsToChange jc190      ON q.UID_Job = jc.UID_Job191    WHERE192      q.UID_Tree IN(193    SELECT t.UID_SingleGuid194    FROM @Trees t)195    SELECT @JobCountTotal = count(*)196    FROM @JobsToChange197    WHILE @JobIndexCurrent <= @JobCountTotal198    BEGIN199      SELECT200        @PostCMD = 'declare @uid_Tree varchar(38) , @alt varchar(1), @neu varchar(1), @UID_Job varchar(38), @CountLines int = 0 ' + char(13) +201        char(10)202      SELECT @StartLenPostCMD = len(@PostCMD)203      SELECT204        TOP 1 @UID_Job = ch.UID_Job,205        @NewState = ch.NewState,206        @SuccesInfo = ch.SuccesInfo,207        @MessageString = ch.MessageString208      FROM @JobsToChange ch209      WHERE210        ch.SortOrder = @JobIndexCurrent211      IF212      RIGHT(rtrim(@UID_Job),213      2) = 'RA'214      BEGIN215        GOTO ZyklusEnde216      END217      IF @SuccesInfo IN(1,218      -1)219      BEGIN220        SELECT221          TOP 1 @UID_NextJob = isnull(sh.UID_JobSuccess,222          ''),223          @UID_NOTnextJob = CASE224          WHEN isnull(sh.UID_JobError,225          '') = isnull(sh.UID_JobSuccess,226          '') THEN227          ''228        ELSE isnull(sh.UID_JobError,229        '')230        END,231        @uid_tree = sh.UID_Tree,232        @isSplitOnly = 1,233        @UID_JobError = sh.UID_JobError,234        @ProcessTrackingForJob = isnull(sh.ProcessTracking,235        0),236        @errorNotify = isnull(sh.ErrorNotify,237        0),238        @successNotify = isnull(sh.SuccessNotify,239        0),240        @UID_JobsameServer = sh.UID_JobSameServer,241        @Retries = isnull(sh.Retries,242        0),243        @DeferOnError = isnull(sh.DeferOnError,244        0),245        @MinutesToDefer = isnull(sh.MinutesToDefer,246        1),247        @MailAddress = sh.NotifyAddress,248        @MailSender = sh.NotifySender,249        @MailSubject = sh.NotifySubject,250        @MailBody = sh.NotifyBody,251        @MailAddress_success = sh.NotifyAddressSuccess,252        @MailSender_success = sh.NotifySenderSuccess,253        @MailSubject_success = sh.NotifySubjectSuccess,254        @MailBody_success = sh.NotifyBodySuccess,255        @JobChainName = isnull(sh.JobChainName,256        ''),257        @Queue = sh.Queue,258        @GenProcID = sh.GenProcID,259        @IsToFreezeOnError = 0,260        @ComponentClass = sh.ComponentClass,261        @TaskName = sh.TaskName,262        @UID_JobOrigin = sh.UID_JobOrigin,263        @ready2exeOld = sh.Ready2EXE,264        @IsForHistory = sh.IsForHistory,265        @IsLastJobOfSubtree = CASE266        WHEN sh.UID_JobSameServer <> ISNULL(ne.UID_JobSameServer,267        '') AND @NewState IN(N 'FINISHED') THEN268        1269        ELSE 0270        END,271        @IsLastJobOfTree = CASE272        WHEN isnull(sh.UID_JobSuccess,273        '') = '' AND @NewState IN(N 'FINISHED') THEN274        1275        ELSE 0276        END277        FROM @JobQueueShadow sh278        LEFT279        OUTER280        JOIN @JobQueueShadow ne281          ON sh.UID_JobSuccess = ne.UID_Job282        WHERE283          sh.UID_Job = @UID_Job284      END285      ELSE286      BEGIN287        SELECT288          TOP 1 @UID_NextJob = isnull(sh.UID_JobError,289          ''),290          @UID_NOTnextJob = CASE291          WHEN isnull(sh.UID_JobError,292          '') = isnull(sh.UID_JobSuccess,293          '') THEN294          ''295        ELSE isnull(sh.UID_JobSuccess,296        '')297        END,298        @uid_tree = sh.UID_Tree,299        @isSplitOnly = isnull(sh.IsSplitOnly,300        0),301        @UID_JobError = sh.UID_JobError,302        @ProcessTrackingForJob = isnull(sh.ProcessTracking,303        0),304        @errorNotify = isnull(sh.ErrorNotify,305        0),306        @successNotify = isnull(sh.SuccessNotify,307        0),308        @UID_JobsameServer = sh.UID_JobSameServer,309        @Retries = isnull(sh.Retries,310        0),311        @DeferOnError = isnull(sh.DeferOnError,312        0),313        @MinutesToDefer = isnull(sh.MinutesToDefer,314        1),315        @MailAddress = sh.NotifyAddress,316        @MailSender = sh.NotifySender,317        @MailSubject = sh.NotifySubject,318        @MailBody = sh.NotifyBody,319        @MailAddress_success = sh.NotifyAddressSuccess,320        @MailSender_success = sh.NotifySenderSuccess,321        @MailSubject_success = sh.NotifySubjectSuccess,322        @MailBody_success = sh.NotifyBodySuccess,323        @JobChainName = isnull(sh.JobChainName,324        ''),325        @Queue = sh.Queue,326        @GenProcID = sh.GenProcID,327        @IsToFreezeOnError = isnull(sh.IsToFreezeOnError,328        0),329        @ComponentClass = sh.ComponentClass,330        @TaskName = sh.TaskName,331        @UID_JobOrigin = sh.UID_JobOrigin,332        @ready2exeOld = sh.Ready2EXE,333        @IsForHistory = sh.IsForHistory,334        @IsLastJobOfSubtree = CASE335        WHEN sh.UID_JobSameServer <> ISNULL(ne.UID_JobSameServer,336        '') AND @NewState IN(N 'FINISHED') THEN337        1338        ELSE 0339        END,340        @IsLastJobOfTree = CASE341        WHEN isnull(sh.UID_JobError,342        '') = '' AND @NewState IN(N 'FINISHED') THEN343        1344        ELSE 0345        END346        FROM @JobQueueShadow sh347        LEFT348        OUTER349        JOIN @JobQueueShadow ne350          ON sh.UID_JobError = ne.UID_Job351        WHERE352          sh.UID_Job = @UID_Job353      END354      IF @@rowcount = 0355      BEGIN356        SELECT357          @errmsg = CONCAT('#LDS#The process step to update is missing. Tree: {0} missing process step: {1}.|' + 'not exists',358          '|',359          @UID_Job,360          '|')361        EXEC QBM_PJournal @errmsg,362          @@procid,363        'W',364          @DebugLevel365      END366      SELECT @ProcessTrackingForTree = @ProcessTrackingForJob367      IF @IsLastJobOfTree = 1368      BEGIN369        IF @ProcessTrackingForTree = 0370        BEGIN371          IF EXISTS(372            SELECT TOP 1 1373            FROM @JobQueueShadow s374            WHERE375              s.UID_Tree = @UID_Tree AND s.ProcessTracking = 1)376          BEGIN377            SELECT @ProcessTrackingForTree = 1378          END379        END380        IF @ComponentClass = N 'VI.JobService.JobComponents.DelayComponent' AND @TaskName = N 'ReActivateJob' AND @WasError = 0381        BEGIN382          SELECT @IsLastJobOfTree = 0383        END384      END385      IF @newState = N 'MISSING'386      BEGIN387        UPDATE @JobQueueShadow388        SET Ready2EXE = N 'MISSING',389        IsToFreezeOnError = CASE390        WHEN @isSplitOnly = 1 THEN391        q.IsToFreezeOnError392        WHEN @UID_JobError > ' ' THEN393        q.IsToFreezeOnError394        ELSE 1395        END,396        IsRecordModified = 1,397        IsRecordModifiedExtended = 1,398        XDateUpdated = @XDateSpecial,399        XUserUpdated = @XUserSpecial400        FROM @JobQueueShadow q401        WHERE402          q.UID_Job = @UID_Job AND q.Ready2EXE IN(N 'LOADED',403        N 'PROCESSING') AND NOT EXISTS(404        SELECT TOP 1 1405        FROM @JobQueueShadow q3406        WHERE407          q3.Ready2EXE IN(N 'DELETE', N 'HISTORY') AND q3.UID_Tree = @UID_Tree)408        UPDATE @JobQueueShadow409        SET Ready2EXE = N 'FINISHED',410        IsRecordModified = 1,411        XDateUpdated = @XDateSpecial,412        XUserUpdated = @XUserSpecial413        FROM @JobQueueShadow q414        WHERE415          q.UID_Job = @UID_Job AND q.Ready2EXE IN(N 'LOADED',416        N 'PROCESSING') AND EXISTS(417        SELECT TOP 1 1418        FROM @JobQueueShadow q3419        WHERE420          q3.Ready2EXE IN(N 'DELETE', N 'HISTORY') AND q3.UID_Tree = @UID_Tree)421        GOTO ZyklusEnde422      END423      IF @ready2exeOld NOT IN(N 'LOADED',424      N 'PROCESSING',425      N 'MISSING') AND NOT(@ComponentClass = N 'VI.JobService.JobComponents.DelayComponent' AND @TaskName = N 'ReActivateJob')426      BEGIN427        SELECT428          @errmsg = CONCAT('#LDS#The process step to update is in wrong state. Tree: {0} process step: {1} state {2}.|',429          @uid_tree,430          '|',431          @UID_Job,432          '|',433          @ready2exeOld,434          '|')435        EXEC QBM_PJournal @errmsg,436          @@procid,437        'W',438          @DebugLevel439        GOTO ZyklusEnde440      END441      IF @SuccesInfo IN(1,442      -1) AND @ComponentClass = N 'VI.JobService.JobComponents.DelayComponent' AND @TaskName = N 'ReActivateJob'443      BEGIN444        IF EXISTS(445          SELECT TOP 1 1446          FROM @JobQueueShadow q447          WHERE448            q.UID_Job = @UID_Job AND q.Ready2EXE IN(N 'FALSE', N 'FINISHED'))449        BEGIN450          GOTO ZyklusEnde451        END452      END453      IF isnull(@GenProcID,454      '') = ''455      BEGIN456        SELECT457          @errmsg = CONCAT('#LDS#Process step generated without GenProcID {0}.|',458          @uid_tree,459          N '|')460        EXEC QBM_PJournal @errmsg,461          @@procid,462        'W',463          @DebugLevel464      END465      IF @SuccesInfo IN(0,466      2) AND @IsToFreezeOnError = 1 AND((@deferOnError = 1 AND @retries = 0) OR @deferOnError = 0)467      BEGIN468        SELECT @IsToFreeze = 1469      END470      ELSE471      BEGIN472        SELECT @IsToFreeze = 0473      END474      IF @newstate = 'FINISHED'475      BEGIN476        IF(@SuccesInfo = 0 AND @deferOnError = 1 AND @retries > 0) OR @SuccesInfo = 2477        BEGIN478          UPDATE @JobQueueShadow479          SET retries = retries - CASE @SuccesInfo480          WHEN 2 THEN481          0482          ELSE 1483          END,484          ready2exe= N 'TRUE',485          startat = dateadd(mi,486          CASE @SuccesInfo487            WHEN 2 THEN488          @Common_Jobservice_RedoDelayMinutes489          ELSE @MinutesToDefer490          END,491          GetUTCDate()),492          IsRecordModified = 1,493          IsRecordModifiedExtended = 1,494          XDateUpdated = @XDateSpecial,495          XUserUpdated = @XUserSpecial496          WHERE497            uid_job = @UID_Job498          UPDATE @JobQueueShadow499          SET ready2exe = N 'FALSE',500          IsRecordModified = 1,501          XDateUpdated = @XDateSpecial,502          XUserUpdated = @XUserSpecial503          WHERE504            ready2exe = N 'LOADED' AND UID_Tree = @UID_Tree505          UPDATE @JobQueueShadow506          SET UID_JobSameServer = n.UID_JobSameServerNew,507          IsRecordModified = 1,508          IsRecordModifiedExtended = 1,509          XDateUpdated = @XDateSpecial,510          XUserUpdated = @XUserSpecial511          FROM dbo.QBM_FTJobQueueSetSameServer(@UID_Job) n512          JOIN @JobQueueShadow q513            ON n.UID_Job = q.UID_Job514          WHERE515            n.UID_JobSameServerNew > ' ' AND n.UID_JobSameServerNew <> q.UID_JobSameServer AND q.UID_Tree = @UID_Tree516          IF @ProcessTrackingForJob = 1517          BEGIN518            SELECT519              @PostCMD = @PostCMD + ' select @UID_Job =  ''' + rtrim(@UID_Job) + ''' 520													, @neu = ''D'' ' + @PostCMDBodyJob521          END522          GOTO ZyklusEnde523        END524        IF(@SuccesInfo = 0 AND @IsSplitonly = 0) OR(@SuccesInfo = 2 AND @retries <= 0)525        BEGIN526          SELECT @WasError = 1527          IF @MessageString > ' '528          BEGIN529            IF EXISTS(530              SELECT TOP 1 1531              FROM job532              WHERE533                uid_job = @UID_JobOrigin AND IsErrorLogToJournal = 1)534            BEGIN535              SELECT TOP 1 @ServerName = Ident_Server536              FROM QBMServer537              WHERE538                QueueName = @Queue539              EXEC QBM_PWriteDialogJournal @MessageString,540                @@PROCID,541                @Queue,542              'E',543                @ServerName544            END545          END546          IF @MessageString > ' ' AND @IsLastJobOfTree = 0547          BEGIN548            UPDATE @JobQueueShadow549            SET ErrorMessages = dbo.QBM_FGIJobQueueMessageAdd(ErrorMessages,550            @MessageString),551            IsRecordModifiedErrorMessage = 1,552            IsRecordModified = 1,553            IsRecordModifiedExtended = 1,554            XDateUpdated = @XDateSpecial,555            XUserUpdated = @XUserSpecial556            WHERE557              uid_job = @UID_Job558          END559          IF @errorNotify = 1 AND @IsSendMail = 1560          BEGIN561            IF isnull(@MailSubject,562            N '') = N ''563            BEGIN564              SELECT565                @MailSubject = CONCAT('#LDS#Job failed -> Queue: {0} Chain: {1}.|',566                @Queue,567                '|',568                @jobchainName,569                '|')570            END571            EXEC QBM_PJobCreate_SendMail @MailSubject,572              @MailBody,573              @MailAddress,574              @MailSender,575              @GenProcID = @GenProcID,576              @AdditionalMessage=@MessageString,577              @AdditionalObjectKeysAffected = DEFAULT578          END579        END580        ELSE581        BEGIN582          SELECT @WasError = 0583          IF @errorNotify = 1 AND @isSendMail = 1 AND isnull(len(@MessageString),584          0) > 0585          BEGIN586            IF isnull(@MailSubject,587            N '') = N ''588            BEGIN589              SELECT590                @MailSubject = CONCAT('#LDS#Job failed -> Queue: {0} Chain: {1}.|',591                @Queue,592                '|',593                @jobchainName,594                '|')595            END596            EXEC QBM_PJobCreate_SendMail @MailSubject,597              @MailBody,598              @MailAddress,599              @MailSender,600              @GenProcID = @GenProcID,601              @AdditionalMessage=@MessageString,602              @AdditionalObjectKeysAffected = DEFAULT603          END604          IF @SuccessNotify = 1 AND @IsSendMail = 1 AND isnull(len(@MessageString),605          0) = 0606          BEGIN607            IF isnull(@MailSubject_success,608            N '') = N ''609            BEGIN610              SELECT611                @MailSubject_success = CONCAT('#LDS#Job succeeded -> Queue: {0} Chain: {1}.|',612                @Queue,613                '|',614                @jobchainName,615                '|')616            END617            EXEC QBM_PJobCreate_SendMail @MailSubject_success,618              @MailBody_success,619              @MailAddress_success,620              @MailSender_success,621              @GenProcID = @GenProcID,622              @AdditionalMessage=@MessageString,623              @AdditionalObjectKeysAffected = DEFAULT624          END625          IF @MessageString > ' '626          BEGIN627            IF EXISTS(628              SELECT TOP 1 1629              FROM job630              WHERE631                uid_job = @UID_JobOrigin AND IsErrorLogToJournal = 1)632            BEGIN633              SELECT TOP 1 @ServerName = Ident_Server634              FROM QBMServer635              WHERE636                QueueName = @Queue637              EXEC QBM_PWriteDialogJournal @MessageString,638                @@PROCID,639                @Queue,640              'W',641                @ServerName642            END643          END644          IF @MessageString > ' ' AND @IsLastJobOfTree = 0645          BEGIN646            UPDATE @JobQueueShadow647            SET ErrorMessages = dbo.QBM_FGIJobQueueMessageAdd(ErrorMessages,648            @MessageString),649            IsRecordModified = 1,650            IsRecordModifiedErrorMessage = 1,651            XDateUpdated = @XDateSpecial,652            XUserUpdated = @XUserSpecial653            WHERE654              uid_job = @UID_Job655          END656        END657        IF @ProcessTrackingForJob = 1658        BEGIN659          IF @WasError =1 AND(@deferOnError = 0 OR @deferOnError = 1 AND @retries = 0)660          BEGIN661            SELECT662              @PostCMD = @PostCMD + ' select @UID_Job =  ''' + rtrim(@UID_Job) + ''' 663													, @neu = ''F'' ' + @PostCMDBodyJob664          END665          IF @WasError =0666          BEGIN667            SELECT668              @PostCMD = @PostCMD + ' select @UID_Job =  ''' + rtrim(@UID_Job) + ''' 669													, @neu = ''E'' ' + @PostCMDBodyJob670          END671        END672        IF @IsToFreeze = 1673        BEGIN674          UPDATE @JobQueueShadow675          SET ready2exe= N 'FROZEN',676          wasError = 1,677          ErrorMessages = dbo.QBM_FGIJobQueueMessageAdd(ErrorMessages,678          @MessageString),679          IsRecordModified = 1,680          IsRecordModifiedExtended = 1,681          IsRecordModifiedErrorMessage = 1,682          XDateUpdated = @XDateSpecial,683          XUserUpdated = @XUserSpecial684          WHERE685            uid_job = @UID_Job686          SELECT @SQLcmd = ' uid_job = ''' + rtrim(@UID_Job) + N ''''687          EXEC QBM_PJobCreate_HOFireEvent 'JobQueue',688            @SQLcmd,689          'FROZENDETECT',690            @GenProcID,691            @ObjectKeysAffected = DEFAULT692          UPDATE @JobQueueShadow693          SET ready2exe = N 'FALSE',694          IsRecordModified = 1,695          IsRecordModifiedExtended = 1,696          XDateUpdated = @XDateSpecial,697          XUserUpdated = @XUserSpecial698          WHERE699            ready2exe = N 'LOADED' AND UID_Tree = @UID_Tree700          UPDATE @JobQueueShadow701          SET uid_jobsameserver = uid_job,702          IsRecordModified = 1,703          IsRecordModifiedExtended = 1,704          XDateUpdated = @XDateSpecial,705          XUserUpdated = @XUserSpecial706          WHERE707            uid_jobsameserver <> uid_job AND UID_Tree = @UID_Tree708          IF @ProcessTrackingForJob = 1709          BEGIN710            SELECT711              @PostCMD = @PostCMD + ' select @UID_Job =  ''' + rtrim(@UID_Job) + ''' 712													, @neu = ''L'' ' + @PostCMDBodyJob713          END714          GOTO ZyklusEnde715        END716        IF @IsLastJobOfTree = 0717        BEGIN718          IF @ComponentClass = N 'VI.JobService.JobComponents.DelayComponent' AND @TaskName = N 'ReActivateJob' AND @WasError = 1 AND @UID_NextJob = ''719          BEGIN720            SELECT @IsLastJobOfTree = 1721          END722        END723        IF @IsLastJobOfTree = 1724        BEGIN725          IF EXISTS(726            SELECT TOP 1 1727            FROM @JobQueueShadow q728            WHERE729              q.UID_Job <> @UID_Job AND q.Ready2EXE IN(N 'LOADED', N 'PROCESSING') AND q.UID_Tree = @UID_Tree)730          BEGIN731            SELECT @ResetCounter = 1732            UPDATE @JobQueueShadow733            SET Ready2EXE = N 'FALSE',734            IsToFreezeOnError = 0,735            IsRecordModified = 1,736            IsRecordModifiedExtended = 1,737            XDateUpdated = @XDateSpecial,738            XUserUpdated = @XUserSpecial739            FROM @JobQueueShadow q740            WHERE741              q.UID_Job <> @UID_Job AND q.Ready2EXE IN(N 'LOADED') AND q.UID_Tree = @UID_Tree742          END743          IF @ProcessTrackingForJob = 1 OR @ProcessTrackingForTree = 1744          BEGIN745            SELECT746              @PostCMD = @PostCMD + ' select @uid_tree = ''' + rtrim(@uid_Tree) + ''' 747													, @alt = ''G''748													, @neu = ''N'' ' + @PostCMDBodyTree749          END750          SELECT @IsAlreadyHistory = 0751          IF @writeHistory IN('ERROR',752          'ALL',753          'ERRORORSELECTED',754          'SELECTED')755          BEGIN756            IF(@writeHistory IN('ERROR', 'ERRORORSELECTED') AND(EXISTS(757              SELECT TOP 1 1758              FROM @JobQueueShadow q759              WHERE760                q.WasError =1 AND q.UID_Tree = @UID_Tree) OR(@SuccesInfo = 0 AND @isSplitOnly = 0))) OR(@writehistory = 'ALL') OR(@writehistory IN('ERRORORSELECTED'761            , 'SELECTED') AND @IsForHistory = 1)762            BEGIN763              SELECT @IsAlreadyHistory = 1764              IF @MessageString > ' '765              BEGIN766                UPDATE @JobQueueShadow767                SET ready2exe = N 'HISTORY',768                IsToFreezeOnError = 0,769                wasError = @WasError,770                ErrorMessages = dbo.QBM_FGIJobQueueMessageAdd(ErrorMessages,771                @MessageString),772                IsRecordModified = 1,773                IsRecordModifiedExtended = 1,774                IsRecordModifiedErrorMessage = 1,775                XDateUpdated = @XDateSpecial,776                XUserUpdated = @XUserSpecial777                WHERE778                  uid_job = @UID_Job779              END780              ELSE781              BEGIN782                UPDATE @JobQueueShadow783                SET ready2exe = N 'HISTORY',784                IsToFreezeOnError = 0,785                wasError = @WasError,786                IsRecordModified = 1,787                IsRecordModifiedExtended = 1,788                XDateUpdated = @XDateSpecial,789                XUserUpdated = @XUserSpecial790                WHERE791                  uid_job = @UID_Job792              END793              IF @ProcessTrackingForJob = 1794              BEGIN795                INSERT INTO DialogProcess(GenProcID,796                BasisObjectType,797                ObjectKey,798                ProcessState,799                XDateInserted,800                XDateUpdated,801                XUserInserted,802                XUserUpdated,803                CustomComment,804                XTouched,805                DisplayName)806                SELECT807                  q.GenProcID,808                  '',809                  '',810                  'E',811                  min(q.XDateInserted),812                  min(q.XDateUpdated),813                  max(q.XUserInserted),814                  max(q.XUserUpdated),815                  '',816                  '',817                  max('#' + 'L' + 'D' + 'S' + '#' + isnull(q.JobChainName, N ''))818                FROM @JobQueueShadow q819                WHERE820                  ready2exe = N 'HISTORY' AND NOT EXISTS(821                SELECT TOP 1 1822                FROM DialogProcess p823                  WITH(nolock)824                WHERE825                  p.GenProcID = q.GenProcID) AND q.GenProcID > ' ' AND q.UID_Tree = @UID_Tree826                GROUP BY q.GenProcID827              END828            END829          END830          IF @IsAlreadyHistory = 0831          BEGIN832            IF @MessageString > ' '833            BEGIN834              UPDATE @JobQueueShadow835              SET ready2exe = N 'DELETE',836              IsToFreezeOnError = 0,837              wasError = @WasError,838              ErrorMessages = dbo.QBM_FGIJobQueueMessageAdd(ErrorMessages,839              @MessageString),840              XDateUpdated = @XDateSpecial,841              XUserUpdated = @XUserSpecial,842              IsRecordModified = 1,843              IsRecordModifiedExtended = 1,844              IsRecordModifiedErrorMessage = 1845              WHERE846                uid_job = @UID_Job847            END848            ELSE849            BEGIN850              UPDATE @JobQueueShadow851              SET ready2exe = N 'DELETE',852              IsToFreezeOnError = 0,853              wasError = @WasError,854              XDateUpdated = @XDateSpecial,855              XUserUpdated = @XUserSpecial,856              IsRecordModified = 1,857              IsRecordModifiedExtended = 1858              WHERE859                uid_job = @UID_Job860            END861          END862        END863        ELSE864        BEGIN865          UPDATE @JobQueueShadow866          SET ready2exe = N 'FINISHED',867          WasError = CASE868          WHEN @SuccesInfo = 0 AND @isSplitOnly = 0 THEN869          1870          ELSE 0871          END,872          XDateUpdated = @XDateSpecial,873          XUserUpdated = @XUserSpecial,874          IsRecordModified = 1875          WHERE876            uid_job = @UID_Job877          UPDATE @JobQueueShadow878          SET ready2exe = N 'TRUE',879          IsRecordModified = 1,880          XDateUpdated = @XDateSpecial,881          XUserUpdated = @XUserSpecial882          WHERE883            uid_job = @UID_NextJob AND(ready2exe = N 'FALSE' OR uid_jobSameserver <> @UID_Jobsameserver) AND UID_Tree = @UID_Tree884          IF @@rowcount = 0885          BEGIN886            IF NOT EXISTS(887              SELECT TOP 1 1888              FROM @JobQueueShadow889              WHERE890                uid_job = @UID_NextJob)891            BEGIN892              SELECT893                @errmsg = CONCAT('#LDS#The next job is missing. Tree: {0} missing job: {0}.|',894                @uid_tree,895                N '|',896                @UID_NextJob,897                N '|')898              EXEC QBM_PJournal @errmsg,899                @@procid,900              'W',901                @DebugLevel902            END903          END904        END905      END906      IF @IsLastJobOfSubtree = 1907      BEGIN908        UPDATE @JobQueueShadow909        SET ready2exe = N 'FALSE',910        IsToFreezeOnError = 0,911        IsRecordModified = 1,912        IsRecordModifiedExtended = 1,913        XDateUpdated = @XDateSpecial,914        XUserUpdated = @XUserSpecial915        FROM @JobQueueShadow s916        WHERE917          s.Ready2EXE IN(N 'LOADED',918        N 'MISSING') AND s.UID_JobSameServer = @UID_JobsameServer AND UID_Tree = @UID_Tree919      END920      IF @newstate = N 'PROCESSING'921      BEGIN922        UPDATE @JobQueueShadow923        SET ready2exe = @newstate,924        XDateUpdated = @XDateSpecial,925        XUserUpdated = @XUserSpecial,926        IsRecordModified = 1927        WHERE928          uid_job = @UID_Job AND ready2exe = N 'LOADED'929        IF @ProcessTrackingForJob = 1930        BEGIN931          SELECT932            @PostCMD = @PostCMD + ' select @UID_Job =  ''' + rtrim(@UID_Job) + ''' 933													, @neu = ''P'' ' + @PostCMDBodyJob934        END935      END936      ZyklusEnde:937      IF len(@PostCMD) > @StartLenPostCMD938      BEGIN939        SELECT @PostCMD = @PostCMD + '940		if @CountLines > 0941		 begin942			select 1943		 end944		'945        INSERT INTO @PostCmdCollection(ContentFull,946        UID1,947        UID2)948        SELECT949          @PostCMD,950          @UID_Tree,951          @GenProcID952      END953      SELECT @JobIndexCurrent += 1954    END955    DELETE @JobQueueShadow956    WHERE957      IsRecordModified = 0958    IF EXISTS(959      SELECT TOP 1 1960      FROM @JobQueueShadow s)961    BEGIN962      IF EXISTS(963        SELECT TOP 1 1964        FROM @JobQueueShadow s965        WHERE966          s.IsRecordModifiedErrorMessage = 1)967      BEGIN968        UPDATE JobQueue969        SET ErrorMessages = CASE s.IsRecordModifiedErrorMessage970        WHEN 1 THEN971        s.ErrorMessages972        ELSE q.ErrorMessages973        END,974        UID_JobSameServer = CASE s.IsRecordModifiedExtended975        WHEN 1 THEN976        s.UID_JobSameServer977        ELSE q.UID_JobSameServer978        END,979        StartAt = CASE s.IsRecordModifiedExtended980        WHEN 1 THEN981        s.StartAt982        ELSE q.StartAt983        END,984        Retries = CASE s.IsRecordModifiedExtended985        WHEN 1 THEN986        s.Retries987        ELSE q.Retries988        END,989        IsToFreezeOnError = CASE s.IsRecordModifiedExtended990        WHEN 1 THEN991        s.IsToFreezeOnError992        ELSE q.IsToFreezeOnError993        END,994        Ready2EXE = s.Ready2EXE,995        WasError = s.WasError,996        XDateUpdated = s.XDateUpdated,997        XUserUpdated = s.XUserUpdated998        FROM JobQueue q999        JOIN @JobQueueShadow s1000          ON s.UID_Job = q.UID_Job1001      END1002      ELSE1003      BEGIN1004        UPDATE JobQueue1005        SET UID_JobSameServer = CASE s.IsRecordModifiedExtended1006        WHEN 1 THEN1007        s.UID_JobSameServer1008        ELSE q.UID_JobSameServer1009        END,1010        StartAt = CASE s.IsRecordModifiedExtended1011        WHEN 1 THEN1012        s.StartAt1013        ELSE q.StartAt1014        END,1015        Retries = CASE s.IsRecordModifiedExtended1016        WHEN 1 THEN1017        s.Retries1018        ELSE q.Retries1019        END,1020        IsToFreezeOnError = CASE s.IsRecordModifiedExtended1021        WHEN 1 THEN1022        s.IsToFreezeOnError1023        ELSE q.IsToFreezeOnError1024        END,1025        Ready2EXE = s.Ready2EXE,1026        WasError = s.WasError,1027        XDateUpdated = s.XDateUpdated,1028        XUserUpdated = s.XUserUpdated1029        FROM JobQueue q1030        JOIN @JobQueueShadow s1031          ON s.UID_Job = q.UID_Job1032      END1033    END1034    SET lock_timeout 10001035    SELECT @PostCmdElementIndex = 11036    SELECT TOP 1 @PostCmdElementCount = count(*)1037    FROM @PostCmdCollection1038    WHILE @PostCmdElementIndex <= @PostCmdElementCount1039    BEGIN1040      SELECT1041        TOP 1 @UID_Tree = c.UID1,1042        @GenProcID = c.UID2,1043        @PostCMD = c.ContentFull1044      FROM @PostCmdCollection c1045      WHERE1046        c.ElementIndex = @PostCmdElementIndex1047      SELECT @CountOfPostCMD = 01048      BEGIN TRY1049        EXEC @CountOfPostCMD = QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @PostCMD,1050          @LockTimeout_ms = DEFAULT,1051          @MaxWaitTimeForLock_s = DEFAULT,1052          @ProcIDForJournal = @@procid,1053          @HandleErrorSilent = 01054      END TRY1055      BEGIN CATCH1056        SELECT @CountOfPostCMD = 01057      END CATCH1058      SELECT @PostCmdElementIndex += 11059    END1060    INSERT INTO @FolgeJobs(Object,1061    GenProcID)1062    SELECT1063      x.Object,1064      x.GenProcID1065    FROM(1066    SELECT1067      sh.UID_Tree AS Object,1068      max(sh.GenProcID) AS GenProcID1069    FROM @JobQueueShadow sh1070    GROUP BY sh.UID_Tree1071    HAVING max(convert(int, sh.IsRecordModified)) > 0 OR max(convert(int, sh.ProcessTracking)) > 0) AS x1072    WHERE1073      EXISTS(1074    SELECT TOP 1 11075    FROM DialogProcessChain ch1076      WITH(forceSeek)1077    WHERE1078      ch.UID_Tree = x.Object)1079    IF EXISTS(1080      SELECT TOP 1 11081      FROM @FolgeJobs)1082    BEGIN1083      EXEC QBM_PDBQueueInsert_Bulk 'QBM-K-CommonProcessChain',1084        @FolgeJobs1085    END1086    SET lock_timeout -11087  END TRY1088  BEGIN CATCH1089    EXEC QBM_PSessionErrorAdd DEFAULT1090    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()1091    RAISERROR(@Rethrow,1092    18,1093    1)1094      WITH NOWAIT1095  END CATCH1096  SET lock_timeout -11097  RETURN1098END