Post reply

Returning error codes when using sp_executesql

  • March 25, 2011 at 5:55 am

    #236733

    Hello,

    Is there anyway to return the error reason from the following statement (in this case lets imagine that the database being referred to does not exist)

    declare @err int,@res int

    exec @err=sp_executesql N'BACKUP LOG [DBName] TO [DBNAME_bak] WITH NOINIT ;set @res=@@error', N'@res int out', @res out

    SELECT @ERR [ERROR], @RES [RES]

    Error message returned =

    Msg 911, Level 16, State 10, Line 1

    Database 'DBNAME' does not exist. Make sure that the name is entered correctly.

    Msg 3013, Level 16, State 1, Line 1

    BACKUP LOG is terminating abnormally.

    Results returned are 0 [Error] and 3013 [RES], I am looking to get 911 [Error] and 3013 [RES]

    Many thanks

  • March 25, 2011 at 9:43 am

    #1303677

    Do you know about TRY/CATCH? You can report on trapped error, but it only sees the last error so you'll lose sight of the "DBNAME does not exist error".

    BEGIN TRY

    DECLARE @err INT,

    @res INT

    EXEC @err= sp_executesql

    N'BACKUP LOG [DBName] TO [DBNAME_bak] WITH NOINIT ;set @res=@@error',

    N'@res int out',

    @res OUT

    SELECT @ERR [ERROR],

    @RES [RES]

    END TRY

    BEGIN CATCH

    SELECT ERROR_LINE() AS [ERROR_LINE],

    ERROR_MESSAGE() AS [ERROR_MESSAGE],

    ERROR_NUMBER() AS [ERROR_NUMBER],

    ERROR_PROCEDURE() AS [ERROR_PROCEDURE],

    ERROR_SEVERITY() AS [ERROR_SEVERITY],

    ERROR_STATE() AS [ERROR_STATE] ;

    END CATCH

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

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

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