I receive database notification mail whenever a job fails. Error message of the failed job (Step Id=0 in the Job History) is included in the mail;

MESSAGES: The job failed. The Job was invoked by User SomeUser. The last step to run was step 1 (FailingStep).

but not the step's failure message (Step Id > 0 in the Job History).
I also want to see the step's message so that I can identify the root cause directly from looking at the mail. For example, step 1's message in the above example.
Is it possible to include a job step's failure message within the alert mail?

  • Are the steps you execute TSQL ? Commented Mar 1, 2016 at 15:19
  • Yes. They mostly run stored procedures. Commented Mar 1, 2016 at 16:40
  • Ok then for the stored procedures, just build a try catch statement, and when your statement fails send an email with sp_send_dbmail with the error message. the others I would suggest putting them into stored procedures or if they are other types of manipulations (ssis powershell,...) sending a mail with their functionality Commented Mar 2, 2016 at 9:57
  • Hmm..I guess than there is no native way of doing it via job properties without changing sp definition. Commented Mar 2, 2016 at 10:44