How to get a better error message back?

Question

How to get a better error message back?

Raymond van Laake

SSCarpal Tunnel

Points: 4212
More actions
January 15, 2015 at 6:40 am

#300398

I run this piece of code:
BACKUP DATABASE template
TO DISK = '\\myserver\Backup\SQL2012\template\template_newdb.bak'
WITH FORMAT,
MEDIANAME = 'LATEST template NEWDB',
NAME = 'Full Backup of template NEWDB'
Then I get the expected message:
Msg 911, Level 16, State 11, Line 48
Database 'template' does not exist. Make sure that the name is entered correctly.
Msg 3013, Level 16, State 1, Line 48
BACKUP DATABASE is terminating abnormally.
Now I run this code:
BEGIN TRY
BACKUP DATABASE template
TO DISK = '\\myserver\Backup\SQL2012\template\template_newdb.bak'
WITH FORMAT,
MEDIANAME = 'LATEST template NEWDB',
NAME = 'Full Backup of template NEWDB'
END TRY
BEGIN CATCH
SET @msg = 'BACKUP template FAILS: ' + ERROR_MESSAGE()
END CATCH
SELECT @msg
I get as message:
BACKUP DATABASE is terminating abnormally.
What must I do to get something like:
Database 'template' does not exist. Make sure that the name is entered correctly.
BACKUP DATABASE is terminating abnormally.
Thanks!

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

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

Grant Fritchey SSC Guru Points: 398715 More actions · Answer 1

I'm not sure. There's nothing in the standard documentation. It almost looks like the first message is nothing but a warning and the second is the actual error. That certainly rings true since it's the message immediately returned as causing the CATCH to fire.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Lowell SSC Guru Points: 323506 More actions · Answer 2

as far as i know, this is a limitation in SQL for the try catch; you can only catch one error, regardless of the number of errors actually thrown.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3476a931-3db7-4017-9fbe-e082f2de2cb0/sql-server-trycatch-inner-exception-message-conundrum?forum=transactsql
....Yes, this is a limitation of error_xxxxx() functions. When there are multiple error messages, they only give information about one of the messages - not always the most informative one.
In the next version of SQL Server, codenamed Denali, there is a new command THROW which you can use in a catch handler, and which re-reraises the error,
...Well, THROW is here now, but we STILL can't capture the inner exception. Calling THROW within a CATCH block will just raise the original errors - both inner and outer - again, defeating the purpose of the CATCH block.
I hope someone can show me otherwise, but I can't seem to get at that first, inner exception message from within T-SQL.

so effectively, it cannot be done from within TSQL, but it can be done done by iterating through the informationmessages of the connection object from within a programming language; that is exactly what SSMS is doing, it shows each error message or info message in the collection, and not just the last, which is what the ERROR_MESSAGE() is limited to .

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 398715 More actions · Answer 3

Excellent. Thanks Lowell. I did not know that.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning