T-SQL: come possiamo eseguire in modo sequenziale tutti gli step in un Job?

Ecco un modo per gestire in maniera sequenziale la lavorazione di tutti gli step all’interno di un solo JOB. 

1) Primo passo da fare è creare un Job con tutti gli step che dovranno essere lavorati e chiamarlo BONUS_TRACK.

2) Creiamo una Stored Procedure chiamata 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.
--- Li utilizzeremo per ridurre le query di stato del job.
---((( 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!
	IF @Fine_Wait = 0
		WAITFOR DELAY @Secs_Inter_T
	--- Contaloop
	SET @IND = @IND + 1
	--- Interrogo statistiche di sistema
	SET @Step_Ret_Code =
		(SELECT TOP 1
			msdb.dbo.sysjobhistory.run_status
		FROM
			msdb.dbo.sysjobhistory INNER JOIN msdb.dbo.sysjobs
			ON msdb.dbo.sysjobhistory.job_id = msdb.dbo.sysjobs.job_id
		WHERE
			msdb.dbo.sysjobhistory.step_name = '(Job outcome)'
			AND msdb.dbo.sysjobs.name = @Nome_Job
			AND CAST(msdb.dbo.sysjobhistory.run_date AS VARCHAR(22)) +
				RIGHT('000000' + CAST(msdb.dbo.sysjobhistory.run_time AS VARCHAR(9)), 6)
				>= @Run_Job_Ini
		ORDER BY msdb.dbo.sysjobhistory.run_date DESC,
			msdb.dbo.sysjobhistory.run_time DESC)
	--- controllo null
	SET @Step_Ret_Code = ISNULL(@Step_Ret_Code, -1)
	SET @Time_Now = GETDATE()
	SET @Secs_Past = DATEDIFF( SECOND, @Run_Job_Ini_D, @Time_Now)
	IF @Secs_Past < @JOB_Secs_Min
		SET @Secs_Inter = @JOB_Secs_Min - @Secs_Past
	IF @Secs_Past >= @JOB_Secs_Min AND @Secs_Past < @JOB_Secs_AVG
		SET @Secs_Inter = (@JOB_Secs_AVG - @JOB_Secs_Min) / 10
	IF @Secs_Past >= @JOB_Secs_AVG
		SET @Secs_Inter = (@JOB_Secs_Max - @JOB_Secs_AVG) / 10
	IF @Secs_Inter < 30
		SET @Secs_Inter = 30
	IF @Secs_Inter > 60
		SET @Secs_Inter = 60
	---((( CONVERSIONE FORMATO tempo
	--- da secondi (INT)
	--- a stringa 'HHH:MM:SS'
	--- le ore sono secondi / 3600
	SET @Secs_Inter_HH = FLOOR (@Secs_Inter / 3600)
	--- i minuti sono i secondi rimanenti dalle ore / 60
	SET @Secs_Inter_MM = FLOOR ((@Secs_Inter - (@Secs_Inter_HH * 3600))/60)
	--- I secondi sono il resto della divisione per 60
	SET @Secs_Inter_SS = @Secs_Inter - (FLOOR (@Secs_Inter / 60) * 60)
	--- CONVERSIONE in STRINGA
	SET @Secs_Inter_T = RIGHT('000' + CAST(@Secs_Inter_HH AS VARCHAR(9)), 3)
	SET @Secs_Inter_T = @Secs_Inter_T + ':' + RIGHT('00' + CAST(@Secs_Inter_MM AS VARCHAR(9)), 2)
	SET @Secs_Inter_T = @Secs_Inter_T + ':' + RIGHT('00' + CAST(@Secs_Inter_SS AS VARCHAR(9)), 2)
	IF @Step_Ret_Code < 0
		SET @Job_Status = 'In esecuzione'
	IF @Step_Ret_Code = 0	
		SET @Job_Status = 'Failed'
	IF @Step_Ret_Code = 1	
		SET @Job_Status = 'Succeeded'
	IF @Step_Ret_Code = 2	
		SET @Job_Status = 'Retray'
	IF @Step_Ret_Code = 3	
		SET @Job_Status = 'Canceled'
	IF @Step_Ret_Code = 4	
		SET @Job_Status = 'In progress'
	--- LOG & MONITOR
	IF @Ind >= @Ind_Mon
		BEGIN
		PRINT CONVERT(VARCHAR(30), GETDATE(), 120)
		SET @Riga = 'Run_Wait_Job - ' + @Nome_Job + ' - (' + CAST(@IND AS VARCHAR(9)) + ')'
		SET @Riga = @Riga + ' - secondi tot: ' + CAST(@Secs_Past AS VARCHAR(9))
		SET @Riga = @Riga + ' - intervallo secs: ' + @Secs_Inter_T
		SET @Riga = @Riga + ' - JOB STATUS: ' + @Job_Status
		PRINT @Riga
		EXEC  [FLUSSI].[dbo].[LogInsert2]  4, @Riga
		SET @Ind_Mon = @Ind_Mon * 2
		END
	--- controllo per uscita dal loop
	IF @Step_Ret_Code >= 0
		SET @Fine_Wait = 1
	IF @Secs_Past > @JOB_Secs_Max
		SET @Fine_Wait = 1
END
---((( FINE LOOP
--- monitoring timeout
IF @Step_Ret_Code < 0
	SET @Job_Status = @Job_Status + ' (Timeout scaduto)'
---PRINT @Riga
---EXEC  [FLUSSI].[dbo].[LogInsert2]  4, @Riga
--- valorizza parametro output
SET @Stato_Job = @Step_Ret_Code
--- 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

RETURN

3) In un nuovo JOB chiamato Lavora_Step inseriamo il seguente codice:

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

Come potete vedere: Run_wait_Job_2 è il nome della Stored procedures, Bonus_Track è il nome del nostro Job iniziale (quello con tanti Step).

Ho inoltre inserito lo status dell’operazione per determinarne l’esito positivo o negativo.

Buon divertimento.

Se volete seguire i post di www.informarea.it potete iscrivervi al suo feed RSS.