Notifier l'opérateur si l'une des étapes du travail échoue

@wqw 's accepté réponse est excellente.

Je l'ai étendu pour ceux qui ont de la Messagerie de Base de données activé pour envoyer un email à un peu plus de détails sur ce qui s'est produit et comment. Intègre également icvader sa réponse sur cette page afin de prendre en compte de nouvelles tentatives.

Doit être vraiment très utile pour ceux d'entre nous qui ont besoin de plus de détails pour juger si une action urgente est nécessaire lorsque hors site/sur appel.

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

Une différence par rapport aux autres réponses sur laquelle il est basé: Ne pas soulever l'ensemble de la tâche comme une erreur.
C'est pour conserver la distinction dans l'histoire de job entre Avorté et Terminée avec des Erreurs.

J'ai la plupart de mes étapes pour réessayer en raison d'une unique fonction translog scénario qui provoque le blocage occasionnel. wqw du poste alerte, même si une étape de ré-essayée avec succès. J'ai fait une adaptation qui ne seront pas d'alerte si une étape n'a pas, mais alors a été un succès sur réessayer.

SELECT  step_id, MIN(run_status)
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))
GROUP BY step_id
HAVING MIN(run_status) <> 1 -- success
IF @@ROWCOUNT <> 0
RAISERROR('FailedStep', 16, 1)

Une amélioration de la réponse ci-dessus, dans le cas où quelqu'un veut utiliser les opérateurs de l'agent sql server pour envoyer un e-mail et utiliser la base de données nom de profil stocké dans la base de données 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 = <Operator 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