Failing an "Execute SQL Task" ?

  • ckempste

    SSCoach

    Points: 17983

    Hi all

    Ive been using the "execute sql task" to run my stored procedures, only to find that if I use the RAISEERROR this does not propogate out as a "failed" task so the DTS just continues on...

    ie.

    A1 -----success----A2---->etc---->

    CREATE PROCEDURE A1 AS

    RAISERROR ('An error occured...',10,5) WITH SETERROR

    GO

    so running this DTS, A1 will continue onto A2 no matter what RAISEERROR i place in.

    Funny enough, a SQL*Server generated error WILL result in a error and stop the DTS.

    Any ideas? im not keen on replacing my tasks with Active-X jobs and checking return values it seems to totally defeat the purpose of the SQL task!?

    Cheers

    Chris.


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • ckempste

    SSCoach

    Points: 17983

    Well Chris you #%%@!$ 🙂

    Played with it a little.. all comes down to the use of the severity level, anything over 10 will raise the error correctly in the DTS and stop the next job running (assuming you are using a success workflow). Of course, read up re severity levels as there are numerous restrictions over 16.

    RAISERROR ('An error occured..',11,1)

    ..so the above works..

    Cheers

    Chris.


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

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

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