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