Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TRY - CATCH with multiple errors Expand / Collapse
Author
Message
Posted Friday, October 8, 2010 1:19 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:30 PM
Points: 379, Visits: 558
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?


Dave Mason
Orange County, FL
Post #1001553
Posted Friday, October 8, 2010 1:30 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:43 AM
Points: 3,309, Visits: 6,700
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
Post #1001563
Posted Friday, October 8, 2010 2:05 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1001585
Posted Friday, October 8, 2010 4:17 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:00 AM
Points: 15,522, Visits: 27,902
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1001647
Posted Tuesday, October 12, 2010 2:25 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:30 PM
Points: 379, Visits: 558
Dave Mason (10/8/2010)
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.



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.


Dave Mason
Orange County, FL
Post #1003211
Posted Tuesday, October 12, 2010 2:42 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 12:57 PM
Points: 576, Visits: 692
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.
Post #1003218
Posted Tuesday, October 12, 2010 3:07 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:30 PM
Points: 379, Visits: 558
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...)


Dave Mason
Orange County, FL
Post #1003233
Posted Tuesday, December 14, 2010 11:07 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:30 PM
Points: 379, Visits: 558
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...


Dave Mason
Orange County, FL
Post #1034598
Posted Thursday, April 17, 2014 9:02 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:30 PM
Points: 379, Visits: 558
Here's a pure t-sql solution to my problem:PJ on Development: Retrieve Error Message

Dave Mason
Orange County, FL
Post #1562706
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse