October 8, 2010 at 1:19 pm
If I open a new window in SSMS, attempt to backup a database, and the operation fails, I get two error messages. Here's a simple example that attempts to backup a database snapshot:
BACKUP DATABASE MyDbSnapshot
TO DISK = 'MyDbSnapshot.bak'
Msg 3002, Level 16, State 1, Line 1
Cannot BACKUP or RESTORE a database snapshot.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
If I try the same db backup in a TRY-CATCH block, I lose the first (and more pertinent) error message:
BEGIN TRY
BACKUP DATABASE MyDbSnapshot
TO DISK = 'MyDbSnapshot.bak'
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
How do you access all of the errors in the CATCH block?
October 8, 2010 at 1:30 pm
The try catch will catch only the last message since usually the first message gives the information and the last one gives the final result (Terminating message).
One thing you could try is to call this from a power shell and pipe the output to a file or something.
That is the only way I can think of.
-Roy
October 8, 2010 at 2:05 pm
As per its documentation, Try Catch only gets the error that passes control to the Catch block. The only way to do what you want is to do it outside of a Try Catch block.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 8, 2010 at 4:17 pm
Also CATCH won't catch syntactical (is that a word) errors, so you may see two errors in fact, but you'll only catch one
"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
October 12, 2010 at 2:25 pm
Dave Mason (10/8/2010)
Msg 3002, Level 16, State 1, Line 1Cannot BACKUP or RESTORE a database snapshot.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Does SQL Server record the above error messages anywhere? (System table? Other?)
It does not appear to be in the error log. Rather, I see the following, which (again) is not pertinent:
BACKUP failed to complete the command BACKUP DATABASE MyDbSnapshot. Check the backup application log for detailed messages.
October 12, 2010 at 2:42 pm
In the step of the job on the Advanced tab, specify an output file (.txt). It will log all errors to that.
You should also see more detail in the step of the job history, but that might be truncated. The output file does not get truncated.
October 12, 2010 at 3:07 pm
Derrick Smith (10/12/2010)
In the step of the job on the Advanced tab, specify an output file (.txt). It will log all errors to that.
Thanks, Derrick. That helps some.
Ideally, what I want to do is have my SQL Job create backups for multiple db's. If a backup for a single db fails, I want to capture all the error messages (including the first error message that actually tells me why it failed), and then proceed to the next db backup.
Oh, and I would prefer to do everything in tsql. (Just a personal preference.) I don't have any SSIS packages and I don't use the built-in maintenance plans--I'd like to keep it that way. (Scathing replies may commence now...)
December 14, 2010 at 11:07 am
BTW,
I found this related submission on MS Connect: Incorrect error number passed to catch block
Please go there and vote for it as an important issue. (You'll have to login with an MS Live account.)
Maybe MS will address it in an upcoming release or service pack...
April 17, 2014 at 9:02 am
Here's a pure t-sql solution to my problem:PJ on Development: Retrieve Error Message
February 3, 2015 at 11:57 pm
As this is very old thread.
But just updating this with the solution that the new keyword THROW introduced in SQL Server 2012 will serve the purpose.
THROW in CATCH block will return multiple errors encountered in TRY block.
Check my blog post here: http://sqlwithmanoj.com/2013/01/24/another-reason-to-use-throw-clause-instead-of-raiserror-sql-server-2012/[/url]
September 14, 2016 at 10:18 am
After all these years, I've found a palatable solution. The design pattern involves these steps:
1. Create and start an Extended Events Session: it will capture sqlserver.error_reported events, filtered primarily by SPID.
2. Execute a statement in a TRY block.
3. Within (or after) the CATCH block, read the XEvents session data.
4. Use the available data to respond to the error(s) as appropriate.
5. Stop and drop the XEvents session.
More details in my blog articles:
• The Unfulfilled Promise of TRY...CATCH
• Enhanced T-SQL Error Handling With Extended Events
• Part 2: Enhanced T-SQL Error Handling With Extended Events
(BTW, since this is an old post in a SQL 2005 forum, it should be noted that XEvents didn't exist in SQL 2005)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply