Post reply

Capturing all Error messages caused by SQL Server code

  • November 18, 2019 at 3:28 pm

    #3698561

    Hello -

    The ERROR_MESSAGE() is capturing only the last message in the error stack but nothing preceding it. But I want to capture all the errors that is seen on SSMS when a query fails to write it to my Log Table.

    In my SQL Server 2017, the below BACKUP command is failing and SSMS shows up 2 error messages - Error 3202 & 3013.

    BACKUP DATABASE [Promodag] TO DISK = N'\\ServerX\SQL\DBA_Full.bak' WITH INIT, CHECKSUM, COMPRESSION,STATS=10

    Error: 
    Msg 3202, Level 16, State 1, Line 1
    Write on "\\ServerX\SQL\DBA_Full.bak" failed: 112(There is not enough space on the disk.)
    Msg 3013, Level 16, State 1, Line 1
    BACKUP DATABASE is terminating abnormally.

    But when executed in TRY CATCH, to capture the error, the

    BEGIN TRY 
    BACKUP DATABASE [Promodag]  TO DISK = N'\\ServerX\Backup\DBA_Full.Bak' WITH INIT, CHECKSUM, COMPRESSION,STATS=10
    END TRY
    BEGIN CATCH
    SELECT ERROR_NUMBER(),ERROR_MESSAGE() AS ErrorMessage
    END CATCH

    Output as table :

    (No column name)ErrorMessage
    3013BACKUP DATABASE is terminating abnormally.

    So, please help on how to concatenate all the error messages in the CATCH block.

    --In 'thoughts'...

    --In 'thoughts'...
    Lonely Rogue

  • November 18, 2019 at 3:35 pm

    #3698571

    This is the only way that I know of doing it.

    John

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

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