DataStream

LANGUAGES: C#

ASP.NET VERSIONS: 1.0 | 1.1

Gain Insight into SQL Errors

Use the SqlException's Errors collection to make sense of what's gone wrong.

By Brian Noyes

When dealing with data access, many things can go wrong. You could have a bad connection to the database or network problems. You could have a malformed connection string. You could have an invalid query. You could lack permissions for the objects you are trying to access. And in fact, multiple problems can happen in the scope of a single query. Errors can be raised by SQL Server itself for any of the problems noted above, or any number of errors can be thrown with the RAISERROR statement in stored procedures.

Using ADO.NET and the SQL Server managed provider, these errors bubble up to your code in the form of a SqlException that gets thrown from the execution of a command, which you should naturally catch and handle appropriately. However, there is a lot of embedded information in a SqlException that many developers fail to take advantage of. The typical approach used to handle the exception involves writing out the error message of the exception to an event log or some other reporting mechanism, possibly with the exception's embedded context information about the location that the exception was thrown within the .NET code. But often overlooked is the context information about what happened down in the bowels of the database where the real error occurred, which can be extremely helpful in debugging and troubleshooting data access problems. Stored procedures can call other stored procedures, and you can have a call stack of errors within the database. Warnings can be raised that do not directly cause a SqlException themselves, but could give further indication of what led to the initial error that did raise the exception.

SqlException Reveals All

The SqlException class has many properties that expose additional context information about the first error that was generated in executing a command. These properties include the Procedure, LineNumber, State, Class, and Number of the error that correspond to the individual parts of an error at the SQL Server level. However, these properties do not always give you the complete picture of what happened within the context of the call because they only tell you about the first error that occurred. Usually this is the root cause and the first thing you need to address, but there could be additional errors or warnings thrown that add understanding to what really went wrong during the execution of the query.

The Message property of a SqlException will contain the error strings for all the errors thrown in the execution of a query, so you do get some direct information about any additional errors or warnings if the error strings are informative enough. But the best source of full context information is the Errors collection on the SqlException object, which is an instance of a SqlErrorCollection object. This collection contains all the errors that occurred in the execution of a command with all the context information for each of the errors. This is where you should be extracting the error information for the deepest understanding of what went wrong. Each entry in the collection is an object of the SqlError type. Figure 1 shows the SqlError properties that tell you everything about the individual errors that occurred.

Figure 1. SqlError properties give you a better understanding of all the information relative to an error or warning that occurred during the execution of a query.

By looping through the Errors collection on any SqlException thrown from your data access code, and outputting the properties of each SqlError object in that collection, you will have a lot better debugging and troubleshooting information available.

In the download code accompanying this article, I have included a simple client application that you can use to experiment with this approach. It allows you to enter a connection string and a query string and execute the query. The code captures any SqlException thrown, copies the Errors collection into an ArrayList, and data binds the ArrayList to a DataGrid for display (see Figure 2). The ArrayList is needed for the sample because although the SqlErrorCollection implements the IEnumerable and ICollection interfaces for easy iteration, it does not implement the IList interface, which is required by the Windows Forms DataGrid for binding.

SqlConnection conn = new SqlConnection(txtConnStr.Text);

SqlCommand cmd = new SqlCommand(txtQuery.Text,conn);

try

{

  conn.Open();

  cmd.ExecuteNonQuery();

}

catch (SqlException sqlEx)

{

  ArrayList al = new ArrayList(sqlEx.Errors);

  dgErrors.DataSource = al;

}

catch (Exception ex)

{

  MessageBox.Show(ex.Message);

}

finally

{

  conn.Close();

}

Figure 2. When you catch a SqlException, you should process the Errors collection on the exception object to obtain full information on all the errors and warnings that were thrown within the scope of the command execution.

The download code also contains a SQL script that you can use to add some dummy stored procedures to one of your databases to see how this works when multiple errors and warnings are being thrown. Figure 3 shows the sample program after having called those stored procedures to generate multiple errors.

Figure 3. The sample program for this article lets you execute queries and see the errors collection in a data-bound grid. In this case, a stored procedure was called that called a couple other procedures, all of which are throwing errors and warning that might be relevant to debugging.

Store the Info

Of course, in a real-world application, you are probably not going to have a DataGrid lying around to data bind the collection of errors into, so you will want to put them out somewhere else so you can use them for troubleshooting and debugging. I highly recommend you look into the Exception Management Application Block (EMAB) at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/emab-rm.asp.

I'll be writing a future column detailing its use, but the EMAB basically lets you easily publish exception information from any of your catch blocks with a single line of code, and allows you to output the exception information to a configurable number of publishers with no changes to your application code. You can dump the information to the event log, an XML file, e-mail, a database, or anything you want to write a custom publisher for.

The code accompanying this article includes another sample project that uses the Exception Management Application Block and a custom publisher for SqlExceptions to output the SqlError information to an XmlFile, and is available for download.

Brian Noyes is a consultant, trainer, speaker, and writer with IDesign, Inc. (http://www.idesign.net), a .NET-focused architecture and design consulting firm. Brian specializes in designing and building data-driven distributed applications. He has over 12 years experience in programming, engineering, and project management, and is a contributing editor for asp.netPRO and other publications. Contact him at mailto:brian.noyes@idesign.net.