The Dutch DevDays 2006 Day 2: SQL Server Error handling
It was an excellent second day at the dutch DevDays. That's also because I won a brand new PSP value pack! :)) Thanks to Global Knowledge!!
Today Bob Beauchemin gave an excellent presentation about error handling in SQL Server 2005. Finally SQL Server has a better exception handling with the new try and the catch block. But wait, it is not what you might expect. When you handle the error with a try and a catch block. The client will never see the error number and the error message. That is because you handled the error. During the presentation Bob explained how to rethrow the original exception (error number+message) to the client. So that the client could see the actual exception. But the error number still cannot be retrieved from the error stack. That also means that the client cannot depend on an error number!
A short summary of the presentation:
- You cannot handle errors in user defined functions;
- Either all of the transactions will be committed, or all of them will be rolled back;
- Use the keyword XACT_STATES to check the transaction status. 1 = Transaction in progress, 0 = No transaction, -1 = This is a doomed transaction;
- Use SQLContext.Pipe.ExecuteAndSend(cmd) to execute and get the most of the exceptions;
- If you write SQLCLR code, just forget @@Error! Because the @@Error always have the value zero;
- Best practice handling exceptions: Use SQlCLR, Use a try and a catch block, handle the error and raise the error. In your .net code use SQLError instead of SQLException;
- For a doomed transaction. If you want to log some information in a log table for instance, first rollback the transaction and then log the information. Otherwise the statement will be terminated before you want to log something.