➤ T-SQL: how can we sequentially execute all the steps in a Job? 🎮

Here is a way to sequentially manage the processing of all the steps within one JOB.

T-SQL: how can we sequentially execute all the steps in a Job?

1) The first step is to create a Job with all the steps that will have to be worked and call it BONUS_TRACK.

2) We create a Stored Procedure called Run_wait_job_2.

CREATE PROCEDURE [dbo].[Run_Wait_Job_2] @Nome_Job AS SYSNAME, @Stato_Job AS INT OUTPUT AS --DECLARE @Nome_Job AS VARCHAR(50) --DECLARE @Stato_Job AS INT --SET @Nome_Job = 'PIPPO' --SET @Stato_Job = 0 ---((( Run_Wait_Job ---((( Run_Wait_Job ---((( Run_Wait_Job ---/// VARIABILI DI LAVORO DECLARE @Riga AS VARCHAR(255) DECLARE @RSql AS VARCHAR(1000) DECLARE @ERR AS INT DECLARE @RC AS INT DECLARE @Ind AS INT DECLARE @Ind_Mon AS INT DECLARE @NumRec AS INT DECLARE @Ret_Code AS INT DECLARE @Run_Job_Ini_D AS DATETIME DECLARE @Run_Job_Ini AS VARCHAR(20) DECLARE @Secs_Past AS INT DECLARE @Secs_Inter AS INT DECLARE @JOB_Secs_Min AS INT DECLARE @JOB_Secs_AVG AS INT DECLARE @JOB_Secs_Max AS INT DECLARE @Step_Ret_Code AS INT DECLARE @Time_Now AS DATETIME DECLARE @Secs_Inter_T AS VARCHAR(9) DECLARE @Secs_Inter_HH AS INT DECLARE @Secs_Inter_MM AS INT DECLARE @Secs_Inter_SS AS INT DECLARE @Fine_Wait AS INT DECLARE @Job_Status AS VARCHAR(40) --- cancello tabella temporanea se esiste IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'zz_Run_Wait_Job') DROP TABLE zz_Run_Wait_Job --- cancello tabella temporanea se esiste IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'zz_Run_Wait_Job_2') DROP TABLE zz_Run_Wait_Job_2 --- cancello tabella temporanea se esiste IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'zz_Run_Wait_Job_3') DROP TABLE zz_Run_Wait_Job_3 ---((( LANCIO JOB ESPORTAZIONE --- memorizzo ora inizio lancio SET @Run_Job_Ini_D = GETDATE() SET @Run_Job_Ini = CAST (DATEPART (YEAR, @Run_Job_Ini_D ) AS VARCHAR(9)) SET @Run_Job_Ini = @Run_Job_Ini + RIGHT('00' + CAST (DATEPART(MONTH, @Run_Job_Ini_D ) AS VARCHAR(9)), 2) SET @Run_Job_Ini = @Run_Job_Ini + RIGHT('00' + CAST (DATEPART(DAY, @Run_Job_Ini_D ) AS VARCHAR(9)), 2) SET @Run_Job_Ini = @Run_Job_Ini + RIGHT('00' + CAST (DATEPART(HOUR, @Run_Job_Ini_D ) AS VARCHAR(9)), 2) SET @Run_Job_Ini = @Run_Job_Ini + RIGHT('00' + CAST (DATEPART(MINUTE, @Run_Job_Ini_D ) AS VARCHAR(9)), 2) SET @Run_Job_Ini = @Run_Job_Ini + RIGHT('00' + CAST (DATEPART(SECOND, @Run_Job_Ini_D ) AS VARCHAR(9)), 2) ---/// PRINT '@Run_Job_Ini: ' + @Run_Job_Ini ---((( RUN! EXEC @Ret_Code = msdb.dbo.sp_start_job @job_name = @Nome_Job ---((( ROUTINE di controllo fine job ---((( ROUTINE di controllo fine job ---((( ROUTINE di controllo fine job SET @IND = 0 SET @Ind_Mon = 1 SET @Secs_Inter = 60 ---((( CREO E POPOLO temporanea steps History SELECT msdb.dbo.sysjobs.NAME AS Nome_Job, msdb.dbo.sysjobhistory.step_name AS Nome_Step, RIGHT('000000' + CAST(msdb.dbo.sysjobhistory.run_duration AS VARCHAR(22)), 6) AS Time_HMS INTO zz_Run_Wait_Job FROM msdb.dbo.sysjobs INNER JOIN msdb.dbo.sysjobhistory ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobhistory.job_id WHERE msdb.dbo.sysjobhistory.step_name = '(Job outcome)' AND msdb.dbo.sysjobs.NAME = @Nome_Job ---((( ... affinamento SELECT Nome_Job, JOB_Secs = (CAST(LEFT(Time_HMS, 2) AS INT) * 3600 + CAST(SUBSTRING(Time_HMS, 3, 2) AS INT) * 60 + CAST(RIGHT(Time_HMS, 2) AS INT) ) INTO zz_Run_Wait_Job_3 FROM zz_Run_Wait_Job ---((( stats tempi run jobs SET @JOB_Secs_Min = (SELECT MIN(JOB_Secs) FROM zz_Run_Wait_Job_3) SET @JOB_Secs_AVG = (SELECT AVG(JOB_Secs) FROM zz_Run_Wait_Job_3) SET @JOB_Secs_Max = (SELECT MAX(JOB_Secs) FROM zz_Run_Wait_Job_3) --- monitoring PRINT CONVERT(VARCHAR(30), GETDATE(), 113) SET @Riga = 'Run_Wait_Job - ' + @Nome_Job + ' - Stats orginal' SET @Riga = @Riga + ' Min: ' + ISNULL(CAST(@JOB_Secs_Min AS VARCHAR(9)), '--') SET @Riga = @Riga + ' AVG: ' + ISNULL(CAST(@JOB_Secs_AVG AS VARCHAR(9)), '--') SET @Riga = @Riga + ' Max: ' + ISNULL(CAST(@JOB_Secs_Max AS VARCHAR(9)), '--') PRINT @Riga EXEC [FLUSSI].[dbo].[LogInsert2] 4, @Riga ---((( ritocco statistiche per margini sicurezza SET @JOB_Secs_Min = @JOB_Secs_Min - @JOB_Secs_Min / 10 SET @JOB_Secs_Max = @JOB_Secs_Max + @JOB_Secs_Max / 10 --- e correzione per valori anomali SET @JOB_Secs_Min = ISNULL(@JOB_Secs_Min, 30) SET @JOB_Secs_Max = ISNULL(@JOB_Secs_Max, 3600) SET @JOB_Secs_AVG = ISNULL(@JOB_Secs_AVG, 60) IF @JOB_Secs_Min < 30 SET @JOB_Secs_Min = 30 IF @JOB_Secs_Max <= @JOB_Secs_Min SET @JOB_Secs_Max = @JOB_Secs_Min * 10 IF @JOB_Secs_Max < 1000 SET @JOB_Secs_Max = 1000 IF @JOB_Secs_AVG < @JOB_Secs_Min SET @JOB_Secs_AVG = (@JOB_Secs_Min + @JOB_Secs_Max) / 2 --- monitoring PRINT CONVERT(VARCHAR(30), GETDATE(), 113) SET @Riga = 'Run_Wait_Job - ' + @Nome_Job + ' - Stats' SET @Riga = @Riga + ' Min: ' + CAST(@JOB_Secs_Min AS VARCHAR(9)) SET @Riga = @Riga + ' AVG: ' + CAST(@JOB_Secs_AVG AS VARCHAR(9)) SET @Riga = @Riga + ' Max: ' + CAST(@JOB_Secs_Max AS VARCHAR(9)) PRINT @Riga EXEC [FLUSSI].[dbo].[LogInsert2] 4, @Riga --- A questo punto abbiamo raccolto dalle statistiche --- di sistema i tempi di esecuzione del job in secondi. --- We will use them to reduce job status queries. ---((( Settings per LOOP SET @Secs_Past = 0 SET @Step_Ret_Code = -1 SET @Secs_Inter_T = '000:00:01' SET @Fine_Wait = 0 ---((( INIZIO LOOP ---((( INIZIO LOOP ---((( INIZIO LOOP WHILE @Fine_Wait = 0 BEGIN --- ATTESA!

3) In a new JOB named Work_Step we insert the following code:

DECLARE @Stato_Job AS INT --- EXPORT_AM_CORP EXEC FLUSSI.dbo.Run_Wait_Job_2 'BONUS_TRACK', @Stato_Job OUTPUT --- Rilancia se job failed IF @Stato_Job = 0 EXEC FLUSSI.dbo.Run_Wait_Job_2 'BONUS_TRACK', @Stato_Job OUTPUT

As you can see: Run_wait_Job_2 is the name of the Stored procedures, Bonus_Track is the name of our initial Job (the one with many Steps).

I also entered it status of the operation to determine its positive or negative outcome.