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.
