Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Hi @Kacalapy ~ I'ld like to recommend in the future to ask each question in its own right and that way we can have specific answers focused on one question at a time. I encourage you to do that with this question.
–
jcolebrand♦Apr 21 '11 at 14:11

3 Answers
3

Alex Kuznetsov has a great chapter in his book Defensive Database Programming (Chapter 8) that covers T-SQL TRY...CATCH, T-SQL transactions & SET XACT_ABORT settings, and using client-side error handling. It will help you a lot in deciding which of the options makes the most sense for what you need to accomplish.

It is available for free at this site. I am in no way affiliated with the company, but I do own the hard copy version of that book.

There are a lot of little details on this subject that are explained very well by Alex.

Per Nick's request... (but not all of this is in the chapter)

In terms of scaling, you need to be brutally honest about which activities need to be in the db code and which should be in the app. Ever notice how fast-executing code tends to come back to designing for a single concern per method?

The easiest way to communicate would be custom error codes (> 50,000). It's also pretty fast. It does mean you'd have to keep the db code and app code in sync. With a custom error code, you can also return useful information in the error message string. Because you have an error code strictly for that situation, you can write a parser in the app code tailored to the error's data format.

Also, which error conditions need retry logic in the database? If you want to retry after X seconds, then you're better off handling that in the app code so the transaction doesn't block as much. If you are only re-submitting a DML operation right away, repeating it in the SP could be more efficient. Keep in mind, though, that you'll have to possibly duplicate code or add a layer of SPs to accomplish a retry.

Really, that's currently the biggest pain with TRY...CATCH logic in SQL Server at the moment. It can be done, but it's a bit of an oaf. Look for some improvements coming to this in SQL Server 2012, especially re-throwing system exceptions (preserving the original error number). Also, there's FORMATMESSAGE, which adds some flexibility in constructing error messages, especially for logging purposes.

Red Gate offers several extremely helpful free E-books, and this one is certainly one of the better ones. Great suggestion.
–
Matt MApr 22 '11 at 12:42

Not all of their books do this, but the free version of Kuznetsov's "Defensive..." book does not contain the last 2 chapters on Transaction Isolation Levels and Developing Modifications that survive concurrency. For me. the content in there was worth the purchase.
–
Phil HelmerApr 23 '11 at 0:59

I use Try/Catch, but I also gather as much information as possible and write that to an errorlog AFTER the rollback. In this example, "LogEvent" is a stored procedure that writes to an EventLog table, containing the details of what happened. GetErrorInfo() is a function call that returns the exact error message.

When an error occurs, the information is gathered, the procedure skips down to the error handling section and issues a rollback. The information is written to the log, then the procedure exits.

Considering the extra procedure/function calls involved, it seems a little over the top. HOWEVER this method is tremendously helpful when trying to debug the problem.