Get detailed error handling information in SQL Server 2005

In SQL Server 2005, you no longer need to check the @@ERROR variable after every statement executed, as was the case in SQL Server 2000. Discover the luxury of using SQL Server 2005's TRY…CATCH statements, which give you access to much more detailed error information.

In a recent article, I demonstrated how to catch errors in SQL Server 2000. The most significant aspect of error handling in SQL Server 2000 is the @@ERROR variable. You must query the value of this variable after every statement execution to ensure no error has occurred that would require the transaction to be rolled back. This approach is cumbersome, and more importantly error prone. In addition, this form of error handling in SQL Server 2000 is limited to only working for certain types of errors. Errors that would abort the transaction or batch cannot be handled, and detailed error information isn't available.

TRY…CATCH

SQL Server 2005 provides the TRY…CATCH construct, which is already present in many modern iterative programming languages, such as Java and C#. This construct gives you access to much more detailed error information through a series of new functions available in the CATCH construct, which include the following:

  • ERROR_NUMBER: returns the error number, and is the same value of @@ERROR.
  • ERROR_SEVERITY: returns the severity level of the error that invoked the CATCH block.
  • ERROR_STATE: returns the state number of the error.
  • ERROR_LINE: returns the line number where the error occurred.
  • ERROR_PROCEDURE: returns the name of the stored procedure or trigger for which the error occurred.
  • ERROR_MESSAGE: returns the full message text of the error.

You can use these functions anywhere inside a CATCH block, and they will return information regarding the error that has occurred. These functions will return the value null outside of the CATCH block.

Weekly SQL tips in your inbox
TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.
Automatically sign up today!

Handling deadlock errors

Let's take a look at an example of how we can use the new error handling capabilities in SQL Server 2005 to handle deadlock situations, which are virtually impossible to handle at the database level in SQL Server 2000.

A deadlock occurs when there is competition for resources on your computer. This situation is not isolated solely for database management systems, and occurs for your operation system, or any other system for which resource contention occurs. A deadlock occurs when one process acquires specific locks on a resource and needs additional resources to complete its task. If another process holds locks on the resources needed by the first process and additionally needs the resources the first process has acquired, a stalemate occurs. Neither process will release its resources, which means that neither process will be able to complete its task.

The good news is that SQL Server has algorithms in place that will arbitrarily pick a loser in this situation, which frees up resources so one process can complete its work. This means that the process that was terminated must be retried. In SQL Server 2000 and earlier, the best you could do to remedy this situation was to code specifically for deadlocks in your business layer and retry your transactions if you detect a deadlock. If you notice trends over time with deadlocks situations, you could include logic in your stored procedures that would set the deadlock priority. This approach allows you to pick the loser in deadlock situations, but it leaves you without any means to retry the terminated processes.

With SQL Server 2005, you can catch the errors at the data layer, so your business layer developer doesn't have to worry about retrying the transaction. If you are able to catch a deadlock error, you will need to retry the statement (probably after a period of time has elapsed so the resources that were acquired can be released).

To demonstrate how these new capabilities work, check out Listing A. The code creates the table I will use to log errors that occur. I want to record all of the information from the error-handling functions, along with the date the error occurred and the database that the error occurred in.

I'll use the code in Listing B to log any errors that occur in our procedures. Notice that you do not have to pass any parameters to the procedure, and that the procedure has access to the error-handling functions described above. This is because you will call this procedure in the execution context of the CATCH block. You can reference these functions at any point in the CATCH block—even when calling other procedures.

Listing C is designed to check specifically for the deadlock error number, which is 1205. If a deadlock occurs when the table FicticiousTable1 is updated, the statement is tried again up to three times. If after three times, the update does not succeed, the attempts to update the table are aborted.

Benefits of error handling in SQL Server 2005

SQL Server 2005 offers a more robust set of tools for handling errors than in previous versions of SQL Server. Deadlocks, which are virtually impossible to handle at the database level in SQL Server 2000, can now be handled with ease. By taking advantage of these new features, you can focus more on IT business strategy development and less on what needs to happen when errors occur.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

By Tim Chapman

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.