Developing Matt

My Technical Journal

Best practices for exception handling and defensive programming in Sql server 05 and 08

leave a comment »

I attended a class taught by Adam Machanic.

I didn’t think that there would be much here, but it turned out to be a very beneficial class.  Thanks Mr. Machanic for taking the time to lay all these things out.  Great stuff.

I’m like most programmers.  I don’t have time to sit and query all the error messages and hammer out a best practices for error handling.  i know i should but i don’t.  I know that sql sometimes terminates the batch and sometimes executes the next line after the error.  I know that I should make my programs more error friendly.  I try, but it’s easy to assume your code will always work when you know a simple typo can bring the world to an end (pardon the dramatization).

Basic classification or error messages:

  • severity <=10:Warning/Error. no real concern
  • Severity 11=16: general non fatal issues (needs to be fixed)
  • severity 17 – 19: resource issues.
  • severity 24: hardware

Sql will execute the next line of code for certain errors.  In other words if you have a batch of statements if it receives an error under number 17 then it will continue to process the next line.  That is simplyfying it to much but the point is that this can be a very dangerous realization.  The example he gave was a deadlock issue he had with a customer where every 3 seconds they had a deadlock.  The customer wasn’t trapping for that error and disregarded any logs on it.    The tendency is to think that a higher error requires more attention, but an error 13 can lose a significant amount of business.  All errors matter.

He talked in some depth about how sql server will execute the next line of code and when that occurs and why that is a dangerous thing.  I didn’t catch the ins and outs but the summary solution is that he always turns

set xact_abort on

which turns on transaction batch aborting on by default…so that any error kills the entire transaction.  This makes your statements work as a batch so that if one line fails the next line doesn’t execute. (one exception: raiseerror command)

We can create our own custom persistent errors in sql server. 

  • sp_addmessage stored procedure
    • custom message number (above 50000: preferably a large number to avoid conflicts with other applications that start at a lower number)
    • Custom error message (including format strings)
    • Severity
    • Log by default
  • You can also use  Raiserror(‘general except’,16,1)  This results in msg50000…which is the generic number.  so if you see this in the error logs then it’s due to a raiserror command.
    • you can also add more parameters like @parameterid
  • example

exec sp_addmessage

@msgnum = 500005,

@severity = 16,

@msgtext ‘problem with products id ‘ + %1 + %1 %1  (this will take the parameters

–raise it by calling

raiseerror(500005, 15 (overrides the default severity), 1(default state)

You can check sys.messages to see which errors are logged

You can make a preexisting error to set it to log (not yet in 2008 but he thinks it will be)

exec sp_altermessage

@messgae_id = 1205,

@parameter = ‘with_log’,

@parameter_value = ‘true’

Bonus trick of raise error.  The buffer queue waits until a certain point in time before executing commands.  This can be a real pain if you are waiting on a stored procedure that is taking an exceptionally long time without any output.  If, when calling raiseerror with nowait you can make it not wait until the buffer is ready for it. This forces the buffer to dump itself.

Exception handling options (what to do when the exception hits)

  • @@error
    • after each statement select @@error
    • but each time it hits it resets the error
    • try this
      • select 1/0 as dividebyzero if @@error <>0 select @@error  –this doesn’t work because @@error resets the error, so no error is reported.
  • Try-Catch
    • sql doesn’t know about it.  it is completely handled by the code.
      • only problem is that then you are required to build something for alerting.
    • inside of this we can check the error_number as well 
  • Specialized error functions
    • error_number
    • error_message
    • error_severity
    • error_state_line
    • they do not reset after each statement.  They are available in any level of the scope block including outside of the scope (calling another stored procedure for example
    • He also recommended a catchrethrow stored procedure that utilized the scoping flexibility of these functions.  All it is a stored procedure that you create and catch these errors somehow (logging, notification, or even just returning the errors. and throw your other stored procs in a try catch with a call to this in the end. 
  • Begin transaction, end transactions
    • check xact_state
    • select @@trancount to see how many transactions occurred to make sure that you didn’t have any sneak in when you didn’t want it to happen.

Written by matt

March 25, 2009 at 3:40 am

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

%d bloggers like this: