11
Mar
10

Chapter 6 — Error Handling in SQL Server and Applications

Bill Graziano breaks into t-sql examples in this chapter after writing a brief 129 word introduction.  I just dig that.  The chapter has 13 separate examples and tutorials on how to handle errors in (or from) SQL Server.

Yesterday I was discussing error handling with a colleague and he mentioned that correct error reporting would account for as much 20 percent of the resources allocated to a solution.  I then retorted with an answer of an additional 20 percent should be allocated to error handling as well.  Clearly 40 percent of the time spent on a project will not be required for error handling however the larger point of my comment was to agree with the point that error handling rarely get’s the type of attention that it should.

I’ve had the opportunity to work with many different types of companies over the years… each of these types of companies view error handling with different levels of importance.  For instance, product companies tend to spend more time, energy and ultimately money on gracefully handling errors than a non-profit or a services based organization, who’s users are largely an internal audience.  Regardless of the business drivers and regardless of the type of organization that you work for, it’s pretty easy to start implementing error handling in your database and overall application.

While the try / catch functionality in SQL Server is long overdue in my opinion, it’s been around now for a few years and it should be used as often as you have the opportunity to use it.  In the chapter, Bill mentions some of the limitations around try / catch that are important to keep in mind.  I recently ran across another limitation while working with a distributed transaction while having a nested try / catch block.  Books-on-line defines the behavior that I encountered as follows:

Uncommittable Transactions and XACT_STATE
If an error generated in a TRY block causes the state of the current transaction to be invalidated, the transaction is classified as an uncommittable transaction. An error that ordinarily ends a transaction outside a TRY block causes a transaction to enter an uncommittable state when the error occurs inside a TRY block. An uncommittable transaction can only perform read operations or a ROLLBACK TRANSACTION. The transaction cannot execute any Transact-SQL statements that would generate a write operation or a COMMIT TRANSACTION. The XACT_STATE function returns a value of -1 if a transaction has been classified as an uncommittable transaction. When a batch finishes, the Database Engine rolls back any active uncommittable transactions. If no error message was sent when the
transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application. This indicates that an uncommittable transaction was detected and rolled back. 

As part of this post, I’m was planning on providing some code snippets that I use to help demonstrate this to clients… but after reading Bill’s chapter and seeing that nearly every example that I use to help demonstrate this is in the book, I’ll leave it up to you, the reader, to review the examples in his chapter.  The only item that he doesn’t cover in this chapter that I have found useful is the ‘WITH_LOG’, ‘TRUE’ option on creating or modifying a message.  The example that I love is the divide by zero error.  I love this example because we’ve all been known to run into it and it’s such common sense thing to understand.  So, in the example of the message that Bill creates in the book, I would modify the creation of the message by adding ‘WITH_LOG”, “TRUE”.  What this will do is write the message to the event log every time it’s encountered!  When I choose to add something to the event log in such a manner it’s usually for an internal (DBA) or (SE) type of audience, so have fun with it.  In the divide by zero error, I like to add another message that makes me cringe every time I read it… something along the lines of “You cannot divide by zero in this stored procedure: %s , doh!…”

Do be careful when using try/catch logic.  It’s really easy to swallow errors and tempting as well.  Swallowing an error message will rarely be beneficial to the product or the service that your database provides.  While it will “sweep and issue under the rug”; the root problem will still exist and make it much more difficult to troubleshoot when everyone’s forgotten why, where or when it happened.

Most importantly, use this in your stored procedures.  Work with your application developers and communicate that you can help providing meaningful error messages back to their applications.

Advertisements

0 Responses to “Chapter 6 — Error Handling in SQL Server and Applications”



  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Chapters

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 13 other followers