THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Maria Zakourdaev

Is your TRY worth catching?

 

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

Published Wednesday, June 06, 2012 4:54 PM by Maria Zakourdaev

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Machanic said:

Using xp_cmdshell to do an external SQLCMD call is a clever approach, but feels just a little bit overwrought. You should definitely file a Connect bug about this so that we can vote -- it seems like yet another big hole in T-SQL's TRY/CATCH system.

June 6, 2012 10:12 AM
 

Maria Zakourdaev said:

June 6, 2012 10:44 AM
 

Alexander Kuznetsov said:

This is a very nice crisp example. Because of gotchas like this and many other similar ones, we avoid using TRY..CATCH in any serious development - it is not good enough.

Instead, we just handle errors on the client: iterating through Errors property of a SqlException is robust and easy.

June 6, 2012 12:10 PM
 

Adam Machanic said:

Alex,

Very interesting. Personally, I do both -- but I didn't consider the issue of TRY/CATCH in T-SQL actually *hiding* information. That is certainly a major concern. Unfortunately some of my processes are currently very dependent upon TRY/CATCH. (I did know about other pitfalls, and have lived with them, but this seems worse.) I'll have to give this some more thought.

June 6, 2012 12:18 PM
 

Alexander Kuznetsov said:

Adam,

I think that for long term projects it is so very much cheaper not to use TRY...CATCH at all - the little amount of time invested in robust error handling in C# will pay off many times over in easier/faster troubleshooting.

BTW, for the same reason we do not use SSIS anywhere in our system any more - IMO long term it is cheaper not to have it at all. What do you think?

June 6, 2012 12:33 PM
 

lit said:

great idea!

June 6, 2012 12:54 PM
 

Adam Machanic said:

Alex,

You're making the assumption that everything is running through C# (or some other system, for that matter). Not the case in the environment in which I'm working -- we have a lot of stuff that's running in T-SQL simply via a scheduler. Yes, the scheduler will record errors, but it won't log them in a nice way like my TRY/CATCH blocks will. I guess I could build my own scheduler or a C#-based wrapper for all of the procs, but ... what a pain. And one that feels so very unnecessary (even if it is necessary).

I also don't like SSIS. Happy to report that there's zero in this environment :-)

June 6, 2012 1:41 PM
 

Antony said:

I've often wondered why they made scalar functions for Error Message, etc, rather than a TVF that gave you a row per error, and the sequence they occured in. Seems to me that would be far more useful.

June 6, 2012 2:13 PM
 

TC said:

Not sure i'd want to open up a security hole like xp_cmdshell to work around error handling, though.

June 6, 2012 2:21 PM
 

Alexander Kuznetsov said:

Antony,

I really like your suggestion.

Adam,

We used to have much more T-SQL scripts than we do now. We are gradually retiring them because in our environment they are more expensive to maintain. If they log errors into a table, we have to look into that table besides analyzing the logs from log4net. That makes troubleshooting slower and more complex.

On the other hand, developing a little module that executes *.sql and logs in a uniform way using log4net is completely trivial, can be done only once, and save effort when troubleshooting.

What do you think?

June 6, 2012 2:37 PM
 

Adam Machanic said:

Alex,

I don't agree. In the logging framework I've built I log way more data than I could, comfortably, with L4N. I'm logging things like resource consumption and query plans on a statement-by-statement basis, all into a table that makes it very easy to do regression and historical trending checks. This is all accomplished by injecting logging T-SQL directly into stored procedures. I don't even know how I'd begin to set that up with something like L4N.

--Adam

June 6, 2012 3:02 PM
 

Alexander Kuznetsov said:

Adam,

This is very interesting. Can you share what percentage of the overall overhead on your server is spent on logging? Which version/edition are you running.

I am planning to upgrade from Enterprise 2008 R2 to Standard 2012, so I am kind of reluctant to add overhead for logging, when I am moving some functionality over to the C# client to make the servers less busy.

June 6, 2012 3:30 PM
 

Adam Machanic said:

Alex, 2008. Overall overhead for logging? I'd estimate < 0.1%. It's barely measurable for most of our processes. (On the order of milliseconds, compared to the processes themselves, which tend to run on the order of minutes.)

June 6, 2012 3:40 PM
 

Madhivanan said:

I had posted this as Question of the month december 2011.

http://beyondrelational.com/modules/2/blogs/70/posts/11004/question-of-the-month-december-2011-how-will-you-capture-multiple-error-messages.aspx

A comment pointing to Erland's error handling post says we can also make use of the output from DBCC OUTPUTBUFFER

June 7, 2012 3:48 AM
 

Alejandro Mesa said:

Hi Maria,

Great post, and a nice trick to capture the whole error collection. Sadly, there is no simple way to do the same just using T-SQL, and that is the reason I once suggested if we could wrap the whole collection as an xml doc.

I could be wrong, but the problem here is not the TRY...CATCH block rather than the ERROR_MESSAGE function. In SQL Server 2012, we can use the new statement THROW inside the CATCH block, and it will re-throw multiple errors and not just the most recent one. Of course, if we do not have a mechanism to traverse the error collection at this side, then there no much we can learn about this collection and here is where Maria's approach could be handy, but you do not want to use xp_cmdshell to invoke every single pice of code in T-SQL.

IMHO, it is time that microsoft do something to allow us to traverse the error collection from T-SQL.

--

AMB

June 7, 2012 9:16 AM
 

Armando Prato said:

I also like Antony's suggestion.  I won't use xp_cmdshell in our products so while Maria's solution is clever, it's untenable for my situation.  I would think this would be a simple enhancement for MS, no?

June 9, 2012 11:55 PM
 

Maria Zakourdaev said:

Well, the connect bug was closed as "won't fix". Indeed, the above is a "small" limitation of the error_message() function. I have got a recommendation to upgrade to 2012 and use new throw() statement. It doesn't help us much, in the battlefield the upgrade is much more complicated than in the theory.

If anyone in MS would think about fixing this bug, the best thing is to return the first error instead, it usually is much more informative than the last one, which only sums all the mess as "your statement has failed".

I love the xp_cmdshell procedure, in our environments it's used quite a lot for replication and other infrastructure flows so I'm continuing to use it.

Thanks for all your feedback!

Maria

June 10, 2012 7:32 AM
 

Ola Hallengren said:

June 10, 2012 12:25 PM
 

Pleeg said:

the use of xp_cmdshell is wors :

1) security holes

2) memory leaks (usually when using file operation.

3) use CLR instead.

February 17, 2013 11:09 AM
 

Peleg said:

one more thing.

try/catch is a great thing but you must be aware of two things :

1) can it cause memory leaks when there is intensive use?

2) when you want to expose to the user (outside of sql server), the real error, you won't be able to use your own RAISERROR which be caught by the user as an error, like real error raised by sql server.

February 26, 2013 7:22 AM
 

David Sumlin said:

I agree that ERROR_MESSAGE should return either xml or be a TVF or some other collection of error messages.

Like Adam, I also use a similar logging framework within all my stored procedures, logging things such as parameters, timing of particular sections of code, etc...along with the handling of errors in a TRY..CATCH block.   This article opened my eyes to something I don't think I realized.

I will suggest that defensive coding techniques could eliminate some of the examples that return multiple errors. (e.g. if you verify that the file exists, before restoring)

And BRAVO! to the folks who have admitted to not using SSIS.  I thought I was the only one who chose to not use it.

March 13, 2013 1:45 AM
 

Brian said:

I've written an article on another way of getting at this information using SQL Server 2008R2

See link below:

http://www.sqlservercentral.com/articles/Restore/109710/

June 13, 2014 8:19 AM

Leave a Comment

(required) 
(required) 
Submit

About Maria Zakourdaev

The shortest word in the English language that contains the letters: abcdef is… feedback! Let me know if I have touched something that is alive in the cosmos.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement