Voici un moyen de gérer séquentiellement le traitement de toutes les étapes au sein d'un JOB.
1) La première étape est de créer un Job avec toutes les étapes qui devront être travaillées et de l'appeler TITRE BONUS.
2) Nous créons une procédure stockée appelée Run_wait_job_2.
CRÉER LA PROCÉDURE [dbo].[Run_Wait_Job_2] @Nome_Job AS SYSNAME, @Stato_Job AS INT SORTIE 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 COMME VARCHAR(255) DECLARE @RSql COMME VARCHAR(1000) DECLARE @ERR COMME INT DECLARE @RC COMME INT DECLARE @Ind COMME INT DECLARE @Ind_Mon COMME INT DECLARE @NumRec COMME INT DECLARE @Ret_Code COMME INT DECLARE @Run_Job_Ini_D COMME DATETIME DECLARE @Run_Job_Ini COMME VARCHAR(20) DECLARE @Sec_INT DECLARE @Sec 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 @HINTER AS_Secs_INT_DECLARE @Secs_INT DECLARE @Fine_Wait AS INT DECLARE @Job_Status AS VARCHAR(9) --- cancello tabella temporanea se esiste IF EXISTS(SELECT TABL E_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_Wait_un_table_Wait_zz_Run_Wait_Wait_zz_Run_TABLE40 SI EXISTE(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'zz_Run_Wait_Job_2') DROP TABLE zz_Run_Wait_Job_2 ---((( LANCIO JOB ESPORTAZIONE --- memorizzo ora inizio lancio_SET ANNÉE, @Run_Job_Ini_D ) AS VARCHAR(3)) SET @Run_Job_Ini = @Run_Job_Ini + RIGHT('3' + CAST (DATEPART(MONTH, @Run_Job_Ini_D ) AS VARCHAR(9)), 00) SET @Run_Jun_IniRIGHT = @R ('9' + CAST (DATEPART(DAY, @Run_Job_Ini_D ) AS VARCHAR(2)), 00) SET @Run_Job_Ini = @Run_Job_Ini + RIGHT('9' + CAST (DATEPART(HOUR, @Run_Job_Ini_D ) AS VARCHAR(2) ), 00) SET @Run_Job_Ini = @Run_Job_Ini + RIGHT('9' + CAST (DATEPART(MINUTE, @Run_Job _Ini_D ) AS VARCHAR(2)), 00) SET @Run_Job_Ini = @Run_Job_Ini + RIGHT('9' + CAST (DATEPART(SECOND, @Run_Job_Ini_D ) AS VARCHAR(2)), 00) ---/// 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 ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobhistory .job_id O msdb.dbo.sysjobhistory.step_name = '(Résultat du travail)' ET 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 exécuter les jobs SET @JOB_Secs_Min = (SELECT MIN(JOB_Secs) FROM zz_Run_Wait_Job_3) SET @JOB_Secs_AVG = (SELECT AVG(JOB_SELECTB) FROM zz_Run_Wait_Job_3) SET @JOB_Secs_AVG = (SELECT AVG_SELECT_JOB_Secs) zz_Run_Wait_Job_3) --- surveillance PRINT CONVERT(VARCHAR(30), GETDATE(), 113) SET @Riga = 'Run_Wait_Job - ' + @Nome_Job + ' - Stats original' 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 30 --- e correzione per valori anomali SET @JOB_Secs_Min = ISNULL(@JOB_Secs_Min, 3600) SET @JOB_Secs_Max = ISNULL(@JOB_Secs_Max, 60) SET @JOB_Secs_AVG = ISNULL(@JOB_Secs_) 30Secs_IFJOin, = 30 SI @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 (@s_SET @JOB_Sec) PRINT CONVERT(VARCHAR(2), GETDATE(), 30) SET @Riga = 'Run_Wait_Job - ' + @Nome_Job + ' - Stats' SET @Riga = @Riga + ' Min: ' + CAST(@JOB_Secs_Min AS VARCHAR(113 )) 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].[LogInsert9] 2, @Riga --- A questo punto abbiamo raccolto dalle statistiche --- di sistema i tempi di esecuzione del job in secondi. --- Nous les utiliserons pour réduire les requêtes sur l'état des travaux. ---((( Paramètres par LOOP SET @Secs_Past = 0 SET @Step_Ret_Code = -1 SET @Secs_Inter_T = '000:00:01' SET @Fine_Wait = 0 ---((( INIZIO LOOP ---((( BOUCLE INIZIO ---((( BOUCLE INIZIO TOUT EN @Fine_Wait = 0 COMMENCER --- ATTESA!
3) Dans un nouveau travail appelé Étape_de_travail nous insérons le code suivant :
DECLARE @Stato_Job AS INT --- EXPORT_AM_CORP EXEC FLUSSI.dbo.Run_Wait_Job_2 'BONUS_TRACK', @Stato_Job SORTIE --- Rilancia se job a échoué SI @Stato_Job = 0 EXEC FLUSSI.dbo @Run_Wait_BON_Job_2 '
Comme vous pouvez le voir : Run_wait_Job_2 est le nom des procédures stockées, Bonus_Track est le nom de notre Job initial (celui avec plusieurs Steps).
je l'ai aussi entré Commande de l'opération pour déterminer son issue positive ou négative.
