Post reply

Running Stored Procedure from agent and shows successful even when it fails

  • November 23, 2015 at 7:16 am

    #399371

    Hello All,

    I am sure someone has come up with this issue before and hoping that some senior members have a solution made for already.

    I have a set of stored procedures and they have TRY and CATCH embedded in to them .. when I execute it form the query windows it runs fine, it errors out when fails. However, when I run the job from the SQL agent, the job shows successful even when the SP error's out. Because of the company rules we don't have Db email profile setup hence cant use the send mail option inside the SP.

    How can I accomplish when the SP errors out , make the SQL agent job fail too?

    To recap - I would like my agent job to show failed when executing the SP throws error.

    Thanks in advance

  • November 23, 2015 at 7:21 am

    #1841719

    Does it really throws an error? Or does it just displays a message as configured in the TRY...CATCH...?

    Are you using THROW?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • November 23, 2015 at 8:53 am

    #1841749

    Sorry - correction - yes- it shows what is configure in CATCH.

    Any help is much appreciated.

    With Regards,

  • November 23, 2015 at 8:59 am

    #1841757

    If it gets into the CATCH, and the CATCH doesn't have a RAISERROR. Then, the agent won't consider the error.

    Either you use RAISERROR or you use THROW. Otherwise, when you catch the error, the procedure will succeed.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • November 23, 2015 at 11:56 am

    #1841815

    Thanks I understand what r u saying.... let me dig through it and come back if I have any questions.

  • November 23, 2015 at 6:29 pm

    #1841904

    Thanks for the guys here and the right guidance, I was able to figure it out.. there is a called "throw" in I believe from 2012 on wards. you just add it in the end of your catch block and it will fail the sql agent job wit the right error.

    Many thanks to all.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply