Notify Operator if ANY step in job fails

Can I (How do I) configure Sql Server 2008 to notify an operator if any step in the Job fails?

I have a Sql Server job with several steps to update data from multiple different sources, followed by one final step which performs several calculations on the data. All of the "data refresh" steps are set to "Go to next step on failure". Generally speaking, if one of the data refreshes fails, I still want the final step to run, but I still want to be notified ab开发者_高级运维out the intermediate failures, so if they fail consistantly, I can investigate.

---

Here is how we do it. We add one last T-SQL step (usually called "check steps") with this

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)

Notice that this code is using tokens in job steps (the $(...) part), so code can't be executed in SSMS as is. It basicly tries to find entries of previous steps of the current job in sysjobhistory and looks for failure statuses.

In Properties->Advanced you can also check Include step output in history to get the message from step failure. Leave the On failure action to Quit the job reporting failure.

---

@wqw 's accepted answer is excellent.

I've extended it for those who have Database Mail enabled to email a bit more detail about exactly what failed and how. Also incorporates icvader's answer on this page to take account of retries.

Should be really helpful for those of us who need more detail to judge whether urgent action is required when offsite/on-call.

DECLARE 

@YourRecipients as varchar(1000) = 'myadminemail@bloatcorp.com'
,@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

One difference from the other answers on which its based: Doesn't raise the whole job as an error. That's to retain the distinction in job history between Aborted and Completed with Errors.

---

An improvement to the above answer, in case somebody wants to use the operators in sql server agent to send email; and use the database profile name stored in 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

---

I have most of my steps set to retry due to a unique translog scenario that causes occasional blocking. wqw's post will alert even if a step has successfully retried. I have made an adaptation that will not alert if a step has failed, but then was a success on retry.

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)

---

Adamantish's answer is the perfect solution (Thank you) : worked flawlessly.. minor edits. As wqw stated before, will not work in SSMS, add this as a last step and run the job.

WHERE instance_id > COALESCE
(
(
SELECT MAX(instance_id) 
FROM msdb.dbo.sysjobhistory
WHERE job_id = '2XXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXXX' AND step_id = 0), 0
)
AND h.job_id = '2XXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXXX'
) 
as agg

---

Go to Job Properties > Notification tab > action to perform when the job completes

under that check the Email checkbox and select "When the job fails" from the dropdown and save the job.

Read 4th point on http://msdn.microsoft.com/en-us/library/ms191130.aspx

If you want to notify an operator by e-mail, check E-mail, select an operator from the list, and then select one of the following:

• When the job succeeds: to notify the operator when the job completes successfully.

• When the job fails: to notify the operator when the job completes unsuccessfully.

• When the job completes: to notify the operator regardless of completion status.

---

in each step add code:

if @@error > 0
EXEC sp_send_dbmail @profile_name='DBATeam',
@recipients=dbadmin@somewhere.com',
@subject='SomeJob SomeStep failed',
@body='This is the body of SomeJob SomeStep failed'