Te damos la bienvenida a proyecto on line, aquí vas a hallar la solucíon que buscas.
Solución:
Así es como lo hacemos. Agregamos un último paso de T-SQL (generalmente llamado “pasos de verificación”) con este
SELECT step_name, message
FROM msdb.dbo.sysjobhistory
WHERE instance_id > COALESCE((SELECT MAX(instance_id) FROM msdb.dbo.sysjobhistory
WHERE job_id = $(ESCAPE_SQUOTE(JOBID)) AND step_id = 0), 0)
AND job_id = $(ESCAPE_SQUOTE(JOBID))
AND run_status <> 1 -- success
IF @@ROWCOUNT <> 0
RAISERROR('Ooops', 16, 1)
Tenga en cuenta que este código utiliza tokens en los pasos del trabajo (el $(...) part), por lo que el código no se puede ejecutar en SSMS tal cual. Básicamente trata de encontrar entradas de pasos anteriores del trabajo actual en sysjobhistory y busca estados de falla.
En Propiedades-> Avanzado también puede verificar Incluir la salida de pasos en el historial para recibir el mensaje del error de paso. Deja el En acción de falla para Salir del trabajo informando fallas.
La respuesta aceptada de @wqw es excelente.
Lo he extendido para aquellos que tienen el Correo electrónico de base de datos habilitado para enviar correos electrónicos con un poco más de detalle sobre exactamente qué falló y cómo. También incorpora la respuesta de icvader en esta página para tener en cuenta los reintentos.
Debería ser realmente útil para aquellos de nosotros que necesitamos más detalles para juzgar si se requiere una acción urgente cuando estamos fuera del sitio o de guardia.
DECLARE
@YourRecipients as varchar(1000) = '[email protected]'
,@YourMailProfileName as varchar(255) = 'Database Mail'
,@Msg as varchar(1000)
,@NumofFails as smallint
,@JobName as varchar(1000)
,@Subj as varchar(1000)
,@i as smallint = 1
---------------Fetch List of Step Errors------------
SELECT *
INTO #Errs
FROM
(
SELECT
rank() over (PARTITION BY step_id ORDER BY step_id) rn
, ROW_NUMBER() over (partition by step_id order by run_date desc, run_time desc) ReverseTryOrder
,j.name job_name
,run_status
, step_id
, step_name
, [message]
FROM msdb.dbo.sysjobhistory h
join msdb.dbo.sysjobs j on j.job_id = h.job_id
WHERE instance_id > COALESCE((SELECT MAX(instance_id) FROM msdb.dbo.sysjobhistory
WHERE job_id = $(ESCAPE_SQUOTE(JOBID)) AND step_id = 0), 0)
AND h.job_id = $(ESCAPE_SQUOTE(JOBID))
) as agg
WHERE ReverseTryOrder = 1 ---Pick the last retry attempt of each step
AND run_status <> 1 -- show only those that didn't succeed
SET @NumofFails = ISNULL(@@ROWCOUNT,0)---Stored here because we'll still need the rowcount after it's reset.
-------------------------If there are any failures assemble email and send ------------------------------------------------
IF @NumofFails <> 0
BEGIN
DECLARE @PluralS as char(1) = CASE WHEN @NumofFails > 1 THEN 's' ELSE '' END ---To make it look like a computer knows English
SELECT top 1 @Subj = 'Job: ' + job_name + ' had ' + CAST(@NumofFails as varchar(3)) + ' step' + @PluralS + ' that failed'
,@Msg = 'The trouble is... ' +CHAR(13) + CHAR(10)+CHAR(13) + CHAR(10)
FROM dbo.#Errs
WHILE @i <= @NumofFails
BEGIN
SELECT @Msg = @Msg + 'Step:' + CAST(step_id as varchar(3)) + ': ' + step_name +CHAR(13) + CHAR(10)
+ [message] +CHAR(13) + CHAR(10)+CHAR(13) + CHAR(10) FROM dbo.#Errs
WHERE rn = @i
SET @i = @i + 1
END
exec msdb.dbo.sp_send_dbmail
@recipients = @YourRecipients,
@subject = @Subj,
@profile_name = @YourMailProfileName,
@body = @Msg
END
Una diferencia con las otras respuestas en las que se basa: no plantea todo el trabajo como un error. Eso es para mantener la distinción en el historial de trabajos entre cancelados y completados con errores.
Una mejora a la respuesta anterior, en caso de que alguien quiera usar los operadores en el agente del servidor SQL para enviar correo electrónico; y use el nombre del perfil de la base de datos almacenado en msdb:
DECLARE @EmailRecipients as varchar(1000)
DECLARE @MailProfileName as varchar(255)
DECLARE @Msg as varchar(1000)
DECLARE @NumofFails as smallint
DECLARE @JobName as varchar(1000)
DECLARE @Subj as varchar(1000)
DECLARE @i as smallint = 1
SELECT @EmailRecipients = email_address
FROM msdb.dbo.sysoperators
WHERE name =
SELECT TOP(1) @MailProfileName = name
FROM msdb.dbo.sysmail_profile
SELECT * INTO #Errs
FROM
(SELECT rank() over (PARTITION BY step_id ORDER BY step_id) rn,
ROW_NUMBER() over (partition by step_id order by run_date desc, run_time desc) ReverseTryOrder,
j.name job_name,
run_status,
step_id,
step_name,
[message]
FROM msdb.dbo.sysjobhistory h
JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
WHERE instance_id > COALESCE((SELECT MAX(instance_id) FROM msdb.dbo.sysjobhistory
WHERE job_id = $(ESCAPE_SQUOTE(JOBID)) AND step_id = 0), 0)
AND h.job_id = $(ESCAPE_SQUOTE(JOBID))
) AS agg
WHERE ReverseTryOrder = 1 ---Pick the last retry attempt of each step
AND run_status <> 1 -- show only those that didn't succeed
SET @NumofFails = ISNULL(@@ROWCOUNT,0)---Stored here because we'll still need the rowcount after it's reset.
IF @NumofFails <> 0
BEGIN
DECLARE @PluralS as char(1) = CASE WHEN @NumofFails > 1 THEN 's' ELSE '' END
SELECT top 1 @Subj = job_name + ':'+ CAST(@NumofFails as varchar(3)) + '''Check Steps'' Report',
@Msg = '''Check Steps'' has reported that one or more Steps failed during execution of ' + job_name + CHAR(13) + CHAR(10)+ CHAR(13) + CHAR(10)
FROM dbo.#Errs
WHILE @i <= @NumofFails
BEGIN
SELECT @Msg = @Msg + 'Step ' + CAST(step_id as varchar(3)) + ': ' + step_name +CHAR(13) + CHAR(10)
+ [message] +CHAR(13) + CHAR(10)+CHAR(13) + CHAR(10)
FROM dbo.#Errs
WHERE rn = @i
SET @i = @i + 1
END
EXEC msdb.dbo.sp_send_dbmail
@recipients = @EmailRecipients,
@subject = @Subj,
@profile_name = @MailProfileName,
@body = @Msg
END
Te invitamos a proteger nuestro quehacer ejecutando un comentario o valorándolo te estamos eternamente agradecidos.