CULTURA

T-SQL: ¿cómo podemos ejecutar secuencialmente todos los pasos en un trabajo?

0 93 Menos de un minuto

Aquí hay una forma de administrar secuencialmente el procesamiento de todos los pasos dentro de una TRABAJO.

servidor SQL

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. 

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) 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 failed
IF @Stato_Job = 0
EXEC FLUSSI.dbo.Run_Wait_Job_2 'BONUS_TRACK', @Stato_Job 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 operación para determinar su resultado positivo o negativo.

Divertirse.

Si quieres seguir las publicaciones de www.informarea.it puedes suscribirte a su RSS Feed.

Publicaciones relacionadas

Acceso: cómo guardar importaciones y ejecutarlas en una macro

Cómo contar celdas con texto en Excel

Transmisión de fútbol: los mejores sitios para ver partidos

Cómo comprobar si una PC es compatible con Windows 10

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Botón volver arriba
Cerrar