Is your TRY worth catching?

Posted by Maria Zakourdaev on SQL Blog See other posts from SQL Blog or by Maria Zakourdaev
Published on Wed, 06 Jun 2012 12:54:00 GMT Indexed on 2012/06/06 16:45 UTC
Read the original article Hit count: 172

Filed under:

 

A very useful error handling TRY/CATCH construct is widely used to catch all execution errors  that do not close the database connection. The biggest downside is that in the 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:

image

Assuming that we are using the TRY and CATCH construct, the ERROR_MESSAGE() function will catch the last message only:

image

To workaround this problem you can prepare a temporary table that will receive the statement output. Execute the statement inside the xp_cmdshell stored procedure, connect back to the SQL Server using the command line utility sqlcmd and redirect it's output into the previously created temp table. 

image

After receiving the output, you will need to parse it to understand whether the statement has finished successfully or failed. It’s quite easy to accomplish as long as you know which statement was executed. In the case of generic executions you can query the output table and search for words like“Msg%Level%State%” that are usually a part of the error message.

Furthermore, you don’t need TRY/CATCH in the above workaround, since the xp_cmdshell procedure always finishes successfully and you can decide whether to fire the RAISERROR statement or not.

 

Yours,

Maria

© SQL Blog or respective owner