Aquí hay una forma de administrar secuencialmente el procesamiento de todos los pasos dentro de una TRABAJO.
1) El primer paso es crear un Trabajo con todos los pasos que habrá que trabajar y llamarlo PISTA EXTRA.
2) Creamos un procedimiento almacenado llamado Run_wait_job_2.
CREAR PROCEDIMIENTO [dbo]. [Run_Wait_Job_2] @Nome_Job COMO SYSNAME, @Stato_Job COMO SALIDA INT COMO --DECLARE @Nome_Job AS VARCHAR (50) --DECLARE @Stato_Job AS INT --SET @Nome_Job = '@ PIPPO' --SET @Nome_Job = '@ PIPPO' 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 DECLARAR @RC AS INT DECLARAR @Ind AS INT DECLARAR @Ind_Mon AS INT DECLARAR @NumRec AS INT DECLAR @Ret_Code AS INT DECLARAR @Run_Job_Ini_D AS DATETIME DECLARAR @Run_Job_Ini AS VARCHAR (20) DECLARAR @Secs AS INT DECLARAR @JOB_Secs_Min AS INT DECLAR @JOB_Secs_AVG AS INT DECLARAR @JOB_Secs_Max AS INT DECLAR @Step_Ret_Code AS INT DECLARAR @Time_Now AS DATETIME DECLAR @Secs_Inter_T AS VARCHAR @Secs_ INTICLARE_ 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 = 'zzit_JRun_Wait_Wabella40) 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 @Run_TJob_Ini AÑO, @Run_Job_Ini_D) AS VARCHAR (3)) SET @Run_Job_Ini = @Run_Job_Ini + RIGHT ('3 '+ CAST (DATEPART (MES, @Run_Job_Ini_D) AS VARCHAR (9)), 00) SET @Run_InJob_Ini ('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) --- /// IMPRIMIR' @Run_Job_Ini: '+ @Run_Job_Ini --- ((((¡EJECUTAR! EXEC @Ret_Code = msdb.dbo.sp_start_job @job_name = @Nome_Job --- (((RUTINA di controllo buen trabajo --- (((RUTINA di controllo buen trabajo --- (((RUTINA di controllo buen trabajo SET @IND = 0 SET @Ind_Mon = 1 SET @Secs_Inter = 60 --- (((CREO E POPOLO temporanea pasos Historial 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.jobhist_id = msdb.dbo.sysjobs.jobhist_id = msdb.dbo.sysjobs.jobhist_id = msdb.dbo.sysjobs.jobhist_id .job_id DONDE msdb.dbo.sysjobhistory.step_name = '(Resultado del trabajo)' Y 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 ejecutar trabajos SET @JOB_Secs_Min = (SELECT MIN (JOB_Secs) FROM zz_Run_Wait_Job_3) SET @JOB_Secs_AVG = (SELECTMax_JOB_Secs) zz_Run_Wait_Job_3) --- monitoreando PRINT CONVERT (VARCHAR (3), GETDATE (), 30) SET @Riga = 'Run_Wait_Job -' + @Nome_Job + '- Estadísticas originales' SET @Riga = @Riga + 'Min:' + ISNULL (CAST (@JOB_Secs_Min AS VARCHAR (113)), '-') SET @Riga = @Riga + 'AVG:' + ISNULL (CAST (@JOB_Secs_AVG AS VARCHAR (9)), '-') SET @Riga = @Riga + 'Max:' + ISNULL (CAST (@JOB_Secs_Max AS VARCHAR (9)), '-') IMPRIMIR @Riga EXEC [FLUSSI]. [Dbo]. [LogInsert9] 2, @Riga --- ( ((ritocco statistiche per margini sicurezza SET @JOB_Secs_Min = @JOB_Secs_Min - @JOB_Secs_Min / 4 SET @JOB_Secs_Max = @JOB_Secs_Max + @JOB_Secs_Max / 10 10 --- e correzione per valuei 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_Secs, 60) IFM = 30 IF @JOB_Secs_Max <= @JOB_Secs_Min SET @JOB_Secs_Max = @JOB_Secs_Min * 30 IF @JOB_Secs_Max <10 SET @JOB_Secs_Max = 1000 IF @JOB_Secs_AVG <@JOB_Secs_Min_SET @JOBGSecs PRINT CONVERT (VARCHAR (1000), GETDATE (), 2) SET @Riga = 'Run_Wait_Job -' + @Nome_Job + '- Estadísticas' SET @Riga = @Riga + 'Min:' + CAST (@JOB_Secs_Min AS VARCHAR (30 )) SET @Riga = @Riga + 'AVG:' + CAST (@JOB_Secs_AVG AS VARCHAR (113)) SET @Riga = @Riga + 'Max:' + CAST (@JOB_Secs_Max AS VARCHAR (9)) IMPRIMIR @Riga EXEC [FLUSSI]. [Dbo]. [LogInsert9] 9, @Riga --- A questo punto abbiamo raccolto dalle statistiche --- di sistema i tempi di esecuzione del job in secondi. --- Los usaremos para reducir las consultas sobre el estado del trabajo. --- (((Configuraciones por 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 MIENTRAS @Fine_Wait = 0 COMIENZO --- ATTESA!
3) En un Nuevo trabajo llamado Work_Step insertamos el siguiente código:
DECLARE @Stato_Job AS INT --- EXPORT_AM_CORP EXEC FLUSSI.dbo.Run_Wait_Job_2 'BONUS_TRACK', @Stato_Job OUTPUT --- Rilancia se job falló IF @Stato_Job = 0 EXEC FLUSSI.dbo.Run_Wait_JobUT_TRACK 'OUTPUT
Como puede ver: Run_wait_Job_2 es el nombre de los procedimientos almacenados, Bonus_Track es el nombre de nuestro trabajo inicial (el que tiene muchos pasos).
Yo tambien entré estado de la operación para determinar su resultado positivo o negativo.
