Catching more then one Error

Hello all, not to sure if this can be done but I am attempting to catch a series of errors with in a single TRY CATCH statement.

For example, the following code triggers a single error ( which work fine)

SET NOCOUNT ON
declare @.errmsg nvarchar(4000)
begin try
select 0/0
end try
begin catch
SET @.errmsg = 'Msg ' +
cast(ERROR_NUMBER() as varchar(20)) + ', Level ' +
cast(ERROR_SEVERITY() as varchar(20)) + ', State ' +
cast(ERROR_STATE() as varchar(20)) + ', Line ' +
cast(ERROR_LINE() as varchar(20)) + ', ' + CHAR(13) + 'Procedure ' +
isnull(ERROR_PROCEDURE(),'') + CHAR(13) + isnull(ERROR_MESSAGE(),'')
end catch
select @.errmsg AS ERRMSG

RESULT:
Msg 8134, Level 16, State 1, Line 4, Procedure Divide by zero error encountered.

But when i execute the same TRY CATCH but use a backup routine that i purposly fail the query result shows the following ( which is what i want...)

Msg 4208, Level 16, State 0, Procedure usp_dbbackup2005_v1_5, Line 599
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.

Msg 3013, Level 16, State 1, Procedure usp_dbbackup2005_v1_5, Line 599
BACKUP LOG is terminating abnormally.

Instead i only get the last error in the series.

Msg 3013, Level 16, State 1, Procedure usp_dbbackup2005_v1_5, Line 599
BACKUP LOG is terminating abnormally.

Any Thoughts?

Thanks

Anybody have any thoughts on this? i can't see why i couldnt grab all the errors within a transaction.

Thanks

DM

|||

With my understanding about Try..Catch block (any language); it always cath one exception object and transform the control to Catch block from the try block (even C#.NET or VB.NET does the same..); I dont think it is logically possible ..

Folks what about your opinion here..

|||You wont get the stack are any further executed command errors in the error functions.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Bummer, you would think you would be able to grab entire stack upon error. Obviously as far as a client is concerned the entire stack is pushed out.( which is what you would see if the statement was executed from a .net client or query analyzer.) so I guess that begs the question; how do you build a solid error handler if you are not provided all the tools to dissect the issue.

Thanks

DM