locked
Force SQL job to succeed even after a step fails in SQL server 2008 RRS feed

  • Question

  • All,

    We are trying to force some jobs to succed even though one step in the job fails randomly.

    Now,we choose the property of the job succeed on failure, it still shows up as warning.

    How do we ignore the warning thrown by the job SQL job??

    Any suggestions??

     

    Thanks

    Kay

    Monday, August 30, 2010 5:59 PM

Answers

  • Hi Kay,

    I don't think so we have any option with which we can restrict SQL Server to throw Warning message once a step of a job fails, SQL has given us option of moving to next step even in case of failure of current executed step.

    Now think in this perspective if we have such feature and someone (ANNOYED DBA) changes your cirtical job in such a way that even on failure of critical step/job it still didn't send you error and later it can be a big mess.

    So I think they way SQL is working is fine it will trow warning and it should be.

     

     


    Regards Gursethi Blog: http://ms-gursethi.blogspot.com/
    • Proposed as answer by GURSETHI Tuesday, August 31, 2010 4:52 AM
    • Marked as answer by YoYo Yu Monday, September 6, 2010 3:35 AM
    Tuesday, August 31, 2010 4:52 AM
  • Kay, If you proceed to the next step on a failure, the job will report success. 

    However, when you look at job history using SQL Server Management Studio, it will show an Information icon.  If you expand the job history to see the steps, the failed step will show a failed icon.

    If the failing step is a T-SQL step, you could wrap it in a TRY-CATCH block to mask the error.   For example, the following would prevent a divide-by-zero error from appearing:

    BEGIN TRY
       SELECT 1 / 0;
    END TRY
    BEGIN CATCH
    END CATCH

    Of course, whether it is wise to hide the error or not is up to you.  But if there is a real problem, it still needs fixing, no matter what the job status says.

    RLF

    • Marked as answer by YoYo Yu Monday, September 6, 2010 3:36 AM
    Tuesday, August 31, 2010 1:54 PM

All replies

  • Hi Kay,

    I don't think so we have any option with which we can restrict SQL Server to throw Warning message once a step of a job fails, SQL has given us option of moving to next step even in case of failure of current executed step.

    Now think in this perspective if we have such feature and someone (ANNOYED DBA) changes your cirtical job in such a way that even on failure of critical step/job it still didn't send you error and later it can be a big mess.

    So I think they way SQL is working is fine it will trow warning and it should be.

     

     


    Regards Gursethi Blog: http://ms-gursethi.blogspot.com/
    • Proposed as answer by GURSETHI Tuesday, August 31, 2010 4:52 AM
    • Marked as answer by YoYo Yu Monday, September 6, 2010 3:35 AM
    Tuesday, August 31, 2010 4:52 AM
  • Hi All,

    We have set Advanced property of a step On Success and On Failure action as Quit the Job Reporting Success.

    But it's giving Warning while executing.

    How can we ignore this warning ?

    Please suggest????

    Thanks in advance.

    • Merged by YoYo Yu Wednesday, September 1, 2010 10:05 AM same issue
    Tuesday, August 31, 2010 6:04 AM
  • Kay, If you proceed to the next step on a failure, the job will report success. 

    However, when you look at job history using SQL Server Management Studio, it will show an Information icon.  If you expand the job history to see the steps, the failed step will show a failed icon.

    If the failing step is a T-SQL step, you could wrap it in a TRY-CATCH block to mask the error.   For example, the following would prevent a divide-by-zero error from appearing:

    BEGIN TRY
       SELECT 1 / 0;
    END TRY
    BEGIN CATCH
    END CATCH

    Of course, whether it is wise to hide the error or not is up to you.  But if there is a real problem, it still needs fixing, no matter what the job status says.

    RLF

    • Marked as answer by YoYo Yu Monday, September 6, 2010 3:36 AM
    Tuesday, August 31, 2010 1:54 PM
  • This is the same topic just discussed in:

    http://social.msdn.microsoft.com/Forums/en/sqltools/thread/36b3bf1f-4ae7-4ca6-b3e7-b05504d24285

    Hope that the response helps.
    RLF

    Tuesday, August 31, 2010 2:29 PM