sql-servernotificationsdatabase-administrationsql-server-agentserver-administration

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 about the intermediate failures, so if they fail consistantly, I can investigate.

Solution

  • 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.