none
How can I send an email on an error in any STEP of a job? RRS feed

  • Question

  • I'm using SQL Server 2008 R2. I have a big job that I will be running each evening, and I would like to receive an email if there is an error on ANY step of the job.

    Currently, I can use the job Notification to send an email when either the entire job completes, or the entire job fails. However, on specific step errors, I'm able to proceed to the next step regardless. This is too broad - I need an error email whenever a STEP fails.

    Is there any way to have an alert email sent to me when there is a failure on any STEP within the job? It's driving me crazy; it seems like there should be a way.

    I have a test job set up where the first step purposefully fails (it queries from a table that does not exist) then continues to a second job, and the second job succeeds. I keep getting SUCCESS! emails but cannot figure out how to have SQL Server send me an additional alert email for that first step when it fails.

     

    Thanks!

    Monday, July 11, 2011 3:29 PM

Answers

  • I have a test job set up where the first step purposefully fails (it queries from a table that does not exist) then continues to a second job, and the second job succeeds. I keep getting SUCCESS! emails but cannot figure out how to have SQL Server send me an additional alert email for that first step when it fails. 

    Hi Skkra,

    For the first step, do you select Go to the next step in the On success action list and Quit the job reporting failure in the On failure action list? In this way, the job fails if the query references a nonexistent table.

    Reference: How to: Set Job Step Success or Failure Flow (SQL Server Management Studio)

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Get or Request Code Sample from Microsoft
    If you have any feedback, please tell us.
    • Marked as answer by KJian_ Monday, July 18, 2011 7:51 AM
    Wednesday, July 13, 2011 6:13 AM