ERROR_MESSAGE not iisplaying the full message

  • Hi,

    I'm using SQL Sever 2014. I'm wondering is it possible to get the ERROR_MESSAGE() statement to display the full error message. I know it only displays the last error if there is more than 1 but I would really like to capture all the error if more than 1 exists inside the catch area ofa sql try catch?

    Comments appreciate,

    J.

  • jellybean (7/26/2016)


    Hi,

    I'm using SQL Sever 2014. I'm wondering is it possible to get the ERROR_MESSAGE() statement to display the full error message. I know it only displays the last error if there is more than 1 but I would really like to capture all the error if more than 1 exists inside the catch area ofa sql try catch?

    Comments appreciate,

    J.

    If your code is using TRY ... CATCH, control is passed to CATCH on the first error ... so there are not multiple messages to be caught. Or am I missing something? Can you provide sample code to demonstrate your request?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Lets use a database restore as an example:

    RESTORE DATABASE TestRestore

    FROM DISK = 'SomeCorrectPath'

    WITH RECOVERY,

    MOVE 'WrongDataName' TO 'D:\SQL2014\Data\TEMPCOPY.mdf',

    MOVE 'WrongLogFile' TO 'E:\SQL2014\Logs\TEMPCOPY.ldf'

    The above will throw an error as the logical file names are wrong. The error thrown is:

    Msg 3234, Level 16, State 2, Line 17

    Logical file 'WrongDataName' is not part of database 'TestRestore'. Use RESTORE FILELISTONLY to list the logical file names.

    Msg 3013, Level 16, State 1, Line 17

    RESTORE DATABASE is terminating abnormally.

    Putting this in a try catch does not help as it only displays the last error as below indicates:

    BEGIN TRY

    RESTORE DATABASE TestRestore

    FROM DISK = 'SomeCorrectPath'

    WITH RECOVERY,

    MOVE 'WrongDataName' TO 'D:\SQL2014\Data\TEMPCOPY.mdf',

    MOVE 'WrongLogFile' TO 'E:\SQL2014\Logs\TEMPCOPY.ldf'

    END TRY

    BEGIN CATCH

    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()

    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();

    DECLARE @ErrorState int= ERROR_STATE();

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)

    END CATCH

    So, ERROR_MESSAGE() only returns the last error. Which is a pity. I'm using Sql server 2014 - surely this can be improved? I want to return as much debugging information as possible...

    Comments/suggestions - much appreciated,

    J.

  • Since you're using 2014, use THROW instead of RAISERROR:

    BEGIN TRY

    RESTORE DATABASE TestRestore

    FROM DISK = 'SomeCorrectPath'

    WITH RECOVERY,

    MOVE 'WrongDataName' TO 'D:\SQL2014\Data\TEMPCOPY.mdf',

    MOVE 'WrongLogFile' TO 'E:\SQL2014\Logs\TEMPCOPY.ldf';

    END TRY

    BEGIN CATCH

    THROW;

    END CATCH;

    The downside of this approach is that THROW will terminate the batch at that point, so if there's code after this that has to execute, this won't help.

    Roland Alexander 
    The Monday Morning DBA 
    There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer

Viewing 4 posts - 1 through 3 (of 3 total)

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