none
Can SQL Agent Return a More Detailed Error Message On Job Failure

    Question

  • I have several jobs that are configured o send an email when it fails.  This works as expected, however, I'd like to have more detail.  Actually, I'd like the email to include the step detail information that available when viewing the job history.

    For example, the standard email appears as:

     

    JOB RUN:     'LOAD_Wikki_wikki_wow' was run on 3/24/2010 at 8:34:30 AM

    DURATION: 1 hours, 0 minutes, 2 seconds
    STATUS: Failed
    MESSAGES: The job failed. The Job was invoked by User JUNK\JUNKSTER.
    The last step to run was step 1 (Im Broken). The job was requested to
    start at step 1 (Im Broken).
    More useful information is available by viewing the step detail in the job history.

     

    Message
    Executed as user: Junk\junkster. Microsoft (R) SQL Server Execute Package
    Utility Version 9.00.4035.00 for 64-bit Copyright (C) Microsoft Corp
    1984-2005. All rights reserved. Started: 8:34:30 AM
    Error: 2010-03-24 09:34:32.00 Code: 0x00000004 Source: Report
    Result Description: The Script returned a failure result. End Error
    DTExec: The package execution returned DTSER_FAILURE (1). Started:
    8:34:30 AM Finished: 9:34:32 AM Elapsed: 3601.69 seconds.
    The package execution failed. The step failed.

    Orlanzo
    Friday, March 26, 2010 3:36 PM

Answers

  • Just an outline of what you can do.  

    1. On the step that produces the error set the following advanced features:

          On success action: Quit the job reporting success
          On failure action: Go to the next step (Intead of Quit the job reporting failure

    2. On the next step (or a named step that you can choose)

        Create a TSQL step to get the data from the sysjobstepslog into a variable.
        Use that data in an sp_send_dbmail to send the message to whoever should receive it.

         On success action: Quit the job reporting failure
         On failure action: Quit the job reporting failure

    The error handling step reports failure in either case, so that the job is marked as failed.

    RLF

     

    • Marked as answer by Orlanzo Monday, March 29, 2010 12:30 PM
    Friday, March 26, 2010 5:13 PM

All replies

  • Just an outline of what you can do.  

    1. On the step that produces the error set the following advanced features:

          On success action: Quit the job reporting success
          On failure action: Go to the next step (Intead of Quit the job reporting failure

    2. On the next step (or a named step that you can choose)

        Create a TSQL step to get the data from the sysjobstepslog into a variable.
        Use that data in an sp_send_dbmail to send the message to whoever should receive it.

         On success action: Quit the job reporting failure
         On failure action: Quit the job reporting failure

    The error handling step reports failure in either case, so that the job is marked as failed.

    RLF

     

    • Marked as answer by Orlanzo Monday, March 29, 2010 12:30 PM
    Friday, March 26, 2010 5:13 PM
  • Russell,

     

    Thanks for the tip.  SQL Agent should have the ability to do this natively.  I appreciate the workaround.


    Orlanzo
    Friday, March 26, 2010 7:08 PM
  • I usually use an output file and have sp_send_dbmail attach that output file. I tend to have two mail steps one having "fail" in subject in whcih I do attach output file, and another "success" where I don't attach it.

    I agree that Agent should have this functionality. Wish for it at connect.microsoft.com, and post back. I will definitely vote for it!


    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    • Proposed as answer by varunragul Friday, March 26, 2010 8:29 PM
    Friday, March 26, 2010 7:29 PM
  • Wish for it at connect.microsoft.com, and post back. I will definitely vote for it!
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi


    Tibor,

    Thanks for the suggestion.  I created the Wish on Connect.  Be sure to vote when you have a chance!


    Orlanzo
    Monday, March 29, 2010 12:29 PM
  • <<Thanks for the suggestion.  I created the Wish on Connect.  Be sure to vote when you have a chance!>>

    Done. Added a comment as well.


    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Monday, March 29, 2010 4:36 PM