SqlException Severity and Exception Handling

AJ_Developer

I'm building a class to standardize the way my company is handling a sqlexception. We're also looking to implement a way of giving the developer a suggestion about how to handle certain exceptions. So what i came up with is because there are over 7000+ difference sql exceptions available, was to use the severity level of an exception to determine whether the procedure should re-attempt the same procedure or the process is just dead and there is no way to complete this process.

What I'm looking for from you guys would be comments on whether or not severity is the way to go and possibly other ways you could potentially think of that would work better.

Thanx

Arnie Rowland

My experience is that just about all attempts to create a common and ubiquitous error handling routine always ends with a unruly mess, and that the long term return on investment is negative.

AJ_Developer

I appreciate your comments.

In a team of 20-30 developers, everybody has different coding habits. So when they're working on an extremely large system, this can also create an "unruly mess". The idea is to come up with a standard which all developers will be required to adopt to keep the code as similar as possible.

Now, what we're looking to do a create a standard for creating a database exception which either wraps a sqlexception or can be thrown even when a SqlException is not present.

In addition to this, we thought it would be nice to categorize sqlexceptions and possibly give the developer a "suggestion" on how to proceed. It will ultimately become the developers choice on how he proceeds. I thought about using the severity as way of categorizing exceptions and what i wanted was feedback on the reliability of the severity levels used in Sql Server 2005.

Thanx

John Gordon

I would think that binning exceptions by SqlState might be the best way to approach this problem if you intend to tackle it. You could start by looking at the class codes, then move toward the subcodes for more information.

If you look at the ODBC errors, for instance:

http://www.microsoft.com/technet/prodtechnol/sql/70/proddocs/diag/part3/75528c16.mspx mfr=true

00 means success, 07 tends to apply to stored procedure calls, 08 means connection issue, IM indicates driver issues, etc.

I feel that severity might not offer you the level of granularity that you would desire for a global exception class (after all, if it was that easy we would have done it that way too). While I understand that you likely only run into a subset of exceptions and thus do not necessarily need to enumerate the full range of possible errors, it seems to me that you might handle it best by using SqlState or SqlState in combination with severity. For instance, timeout expired, this particular error should not be retried directly or aborted in most cases. In the case of command timeouts, you would want to: log the issue as a performance issue with a query, reconnect, raise the timeout, and retry.

There is some need to add context to these exceptions. For instance, a timeout exception in a long running query might have a different severity from a timeout exception in connecting and they would be handled differently. The level of granularity necessary will, however, depend entirely on your situation.

Hope this helps,

John (MSFT)

Peter Ritchie

Arnie Rowland wrote:

My experience is that just about all attempts to create a common and ubiquitous error handling routine always ends with a unruly mess, and that the long term return on investment is negative.

It sounds like he's looking for design guidelines, not necesarily common code.

Peter Ritchie

My experience has been that it's rarely as cut-and-dry as testing a severity level. In many cases you don't know how long reattempting will take; in which case you run the risk of having your application appear unresponsive. In most of these cases you want to inform the user there was a problem and ask them if they want to retry (if retrying is an option). This is not something that should be the concern of an exception class though as there will be localization, context, threading, etc. concerns that need to be take into account that would require that exception class to be coupled to too many unrelated concerns.

I would go so far as to set some design guidelines on how to test the severity level and maybe design a common class for interacting with the user.