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.
