Post reply

SQL Job with TSQL Step failing but job history indicates success when used try catch in TSQL

  • May 6, 2010 at 4:06 am

    #138910

    Hi Friends,

    I am running a SQL Agent job which comprises an SP call. In side of the SP, try catch i have used. when any error occured, the control is shifting to catch block and indicates the SP execution is success and eventually the job step and totally the job is success when i look at the history. Now i would like to see the job history as failed when the control in side the SP is going to catch block. How to get this functionality. Could any one of you help me in this regard.

    Thanks in advance for your help.

    Thanks & Regards,

    Venkat.

  • May 6, 2010 at 5:42 am

    #1163085

    Hi Friends,

    My self got resolution to this kind of problem. below is the way i need to alter the SP so as to keep the job fail even though the try catch is used in TSQL

    alter procedure xyz

    AS

    BEGIN

    BEGIN TRY

    SELECT 1/0

    END TRY

    BEGIN CATCH

    select ERROR_MESSAGE()

    RAISERROR('ASFASDF',12,5)

    --SELECT 5/0

    END CATCH

    END

Viewing 2 posts - 1 through 1 (of 1 total)

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