dbo.QBM_PJobRestart

1CREATE PROCEDURE QBM_PJobRestart(2  @DestJobName nvarchar(255),3  @uid_job varchar(38),4  @MinutesToDelay int5)6AS7BEGIN8  DECLARE @uid_destjob varchar(38)9  DECLARE @errmsg nvarchar(255)10  DECLARE @Nachfolger QBM_YSingleGUID11  DECLARE @CurrentTree QBM_YJobQueueShadow12  DECLARE @CountItems int13  DECLARE @MinutesToDelay_intern int14  DECLARE @XUser nvarchar(64) = object_name(@@procid)15  DECLARE @Xdate datetime = getutcdate()16  DECLARE @UID_Tree varchar(38)17  DECLARE @SQL nvarchar(max)18  SET XACT_ABORT OFF19  BEGIN TRY20    IF isnull(@MinutesToDelay,21    0) <= 022    BEGIN23      SELECT @MinutesToDelay_intern = 024    END25    ELSE26    BEGIN27      SELECT @MinutesToDelay_intern = @MinutesToDelay28    END29    SELECT TOP 1 @UID_Tree = q.UID_Tree30    FROM JobQueue q31      WITH(readpast)32    WHERE33      q.UID_Job = @uid_job34    IF @UID_Tree IS NULL35    BEGIN36      SELECT37        @errmsg = '#LDS#Process chain to reactivate not found. ({0}).|' + @uid_job + N '|'38      RAISERROR(@errmsg,39      18,40      1)41        WITH nowait42    END43    UPDATE JobQueue44    SET XUserUpdated = 'prepare QBM_PJobRestart'45    FROM JobQueue q46    WHERE47      q.UID_Tree = @UID_Tree48    INSERT INTO @CurrentTree(UID_Job,49    Ready2EXE,50    StartAt,51    UID_JobSameServer,52    UID_JobError,53    UID_JobOrigin,54    UID_JobSuccess,55    UID_Tree)56    SELECT57      q.UID_Job,58      q.Ready2EXE,59      q.StartAt,60      q.UID_JobSameServer,61      UID_JobError,62      UID_JobOrigin,63      UID_JobSuccess,64      UID_Tree65    FROM JobQueue q66    WHERE67      q.UID_Tree = @UID_Tree68    SELECT @uid_destjob = NULL69    SELECT TOP 1 @uid_destjob = qz.UID_Job70    FROM @CurrentTree q71    JOIN Job j72      WITH(readpast)73      ON q.UID_JobOrigin = j.UID_Job AND q.UID_Job = @uid_job74    JOIN Job jd75      WITH(readpast)76      ON jd.UID_JobChain = j.UID_JobChain AND jd.Name = @DestJobName77    JOIN @CurrentTree qz78      ON qz.uid_jobOrigin = jd.uid_job79    IF @uid_destjob IS NULL80    BEGIN81      SELECT82        @errmsg = '#LDS#Process step to reactivate not found. ({0}).|' + @DestJobName + N '|'83      RAISERROR(@errmsg,84      18,85      1)86        WITH nowait87    END88    INSERT INTO @Nachfolger(UID_SingleGuid)89    SELECT @uid_destjob90    SELECT @CountItems = 191    WHILE @CountItems > 092    BEGIN93      INSERT INTO @Nachfolger(UID_SingleGuid)94      SELECT95        DISTINCT q.UID_JobSuccess96      FROM @CurrentTree q97      WHERE98        EXISTS(99      SELECT TOP 1 1100      FROM @Nachfolger x101      WHERE102        x.UID_SingleGuid = q.UID_Job) AND NOT EXISTS(103      SELECT TOP 1 1104      FROM @Nachfolger x105      WHERE106        x.UID_SingleGuid = q.UID_JobSuccess) AND q.UID_JobSuccess > ' '107      SELECT @CountItems = @@rowcount108      INSERT INTO @Nachfolger(UID_SingleGuid)109      SELECT110        DISTINCT q.UID_JobError111      FROM @CurrentTree q112      WHERE113        EXISTS(114      SELECT TOP 1 1115      FROM @Nachfolger x116      WHERE117        x.UID_SingleGuid = q.uid_job) AND NOT EXISTS(118      SELECT TOP 1 1119      FROM @Nachfolger x120      WHERE121        x.UID_SingleGuid = q.UID_JobError) AND q.UID_JobError > ' '122      SELECT @CountItems += @@rowcount123    END124    UPDATE @CurrentTree125    SET Ready2Exe = N 'FINISHED',126    IsRecordModified = 1127    FROM @CurrentTree q128    WHERE129      q.Ready2EXE IN(N 'MISSING',130    N 'LOADED',131    N 'PROCESSING',132    N 'TRUE') AND NOT EXISTS(133    SELECT TOP 1 1134    FROM @Nachfolger x135    WHERE136      x.UID_SingleGuid = q.UID_Job)137    UPDATE @CurrentTree138    SET Ready2Exe = N 'FALSE',139    UID_JobSameServer = q.UID_Job,140    IsRecordModified = 1141    FROM @CurrentTree q142    WHERE143      (q.UID_Job IN(144    SELECT n.UID_SingleGuid145    FROM @nachfolger n) OR q.Ready2EXE IN(N 'MISSING', N 'LOADED', N 'PROCESSING')) AND q.UID_Job <> @uid_destjob146    UPDATE @CurrentTree147    SET Ready2Exe = N 'TRUE',148    StartAt = dateadd(mi,149    @MinutesToDelay_intern,150    GetUTCDate()),151    UID_JobSameServer = q.UID_Job,152    IsRecordModified = 1153    FROM @CurrentTree q154    WHERE155      q.UID_Job = @uid_destjob156    IF EXISTS(157      SELECT TOP 1 1158      FROM @CurrentTree q159      WHERE160        q.Ready2EXE NOT IN(N 'TRUE', N 'FALSE', N 'FINISHED'))161    BEGIN162      UPDATE @CurrentTree163      SET UID_JobSameServer = q.UID_Job,164      IsRecordModified = 1165      FROM @CurrentTree q166      WHERE167        q.UID_JobSameServer <> q.UID_Job168    END169    SELECT170      TOP 1 @SQL = STRING_AGG(convert(nvarchar(max), x.zeile),171      ';')172    FROM(173    SELECT174      CONCAT('update JobQueue175		set UID_JobSameServer = ''', cu.UID_JobSameServer, '''176			, StartAt = ''',177      dbo.QBM_FCVDatetimeToString(cu.StartAt), '''178			, Ready2EXE = ''', cu.Ready2Exe, '''179			--34101180			, XDateUpdated = ''',181      dbo.QBM_FCVDatetimeToString(@Xdate), '''182			, XUserUpdated = ''', @XUser, '''183		where UID_Job = ''', cu.UID_Job,184      '''185				') AS zeile186    FROM @CurrentTree cu187    WHERE188      cu.IsRecordModified = 1) AS x189    EXEC QBM_PExecuteSQLWithRetry_LLP @SQL190  END TRY191  BEGIN CATCH192    EXEC QBM_PSessionErrorAdd DEFAULT193    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()194    RAISERROR(@Rethrow,195    18,196    1)197      WITH NOWAIT198  END CATCH199  endLabel:200  SET deadlock_priority normal201  RETURN202END