Search code examples
sql-serversql-server-2005error-handlingtry-catch

SQL Server 2005 - Error_Message() not showing full message

I have encapsulated a backup database command in a Try/Catch and it appears that the error message is being lost somewhere. For example:

BACKUP DATABASE NonExistantDB TO DISK = 'C:\TEMP\NonExistantDB.bak'

..gives error:
Could not locate entry in sysdatabases for database 'NonExistantDB'. No entry found with that name. Make sure that the name is entered correctly. BACKUP DATABASE is terminating abnormally.

Whereas:

BEGIN TRY
    BACKUP DATABASE NonExistantDB TO DISK = 'C:\TEMP\NonExistantDB.bak'
END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
END CATCH

... only gives error: BACKUP DATABASE is terminating abnormally.

Is there a way to get the full error message or is this a limitation of try/catch?

Solution

  • It's a limitation of try/catch.

    If you look carefully at the error generated by executing 

     BACKUP DATABASE NonExistantDB TO DISK = 'C:\TEMP\NonExistantDB.bak'

    you'll find that there are two errors that get thrown. The first is msg 911, which states

    Could not locate entry in sysdatabases for database 'NonExistantDB'. No entry found with that name. Make sure that the name is entered correctly.

    The second is the 3013 message that you are displaying. Basically, SQL is only returning the last error.