TRY/ CATCH construct catches last error only - by Maria Zakourdaev

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


36
0
Sign in
to vote
ID 746979 Comments
Status Closed Workarounds
Type Bug Repros 2
Opened 6/6/2012 7:41:57 AM
Access Restriction Public

Description

In case of multiple errors the TRY/CATCH mechanism will only catch the last error.

An example of this can be seen during a standard restore operation. In this example I attempt to perform a restore from a file that no longer exists. Two errors are being fired: 3201 and 3013:

RESTORE DATABASE TEST FROM DISK = 'c:\test\test.bak'

If I use the TRY/CATCH block, the ERROR_MESSAGE() shows only last error, which is "RESTORE DATABASE is terminating abnormally." 
It's very difficult to understand, in such case, why the RESTORE command had failed.

BEGIN TRY 
	RESTORE DATABASE TEST FROM DISK = 'c:\test\test.bak'
END TRY
BEGIN CATCH
	PRINT ERROR_MESSAGE()
END CATCH
Sign in to post a comment.
Posted by Ola Hallengren on 6/10/2012 at 9:39 AM
One solution could be to let the user pass a parameter to the error functions, selecting which error message that should be returned.

ERROR_MESSAGE() should give the last error message as today.
ERROR_MESSAGE(2) should give the second last error message and so on.

This way you could loop through the error messages until the function returns NULL.

The other error functions should work the same way.
Posted by Maria Zakourdaev on 6/10/2012 at 1:34 AM
Thank you for your fast reply. Unfortunately, it will take a few years for the big datacenters to upgrade to the 2012.
I personally think that the first error is much more informative than a last error, which is, in case of several errors, usually just sums all the mess as " statement had failed".

---
Maria
Posted by Microsoft on 6/7/2012 at 11:21 AM
Hello,
Thanks for your feedback. This is more of a limitation with the ERROR functions since they can return only one error & hence the last one. In SQL Server 2012, you can use the new THROW statement in the CATCH block to rethrow the errors and process them on the client-side.

BEGIN TRY
RESTORE DATABASE TEST FROM DISK = 'c:\test\test.bak'
END TRY
BEGIN CATCH
THROW;
END CATCH

We do have other requests that asks for better functions to track call stack & error stack. We will look at this as part of that.

--
Umachandar, SQL Programmability Team
Posted by Antony Clark on 6/6/2012 at 11:16 AM
Could we have a TVF exposing the error info that the scalar functions do, but for all the errors that were raised in the TRY?