DataStream

LANGUAGES: C#

ASP.NET VERSIONS: 1.0 | 1.1

Gain Insight into SQL Errors

Use the SqlException's Errors collection to make senseof 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 couldhave a malformed connection string. You could have an invalid query. You couldlack 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 beraised by SQL Server itself for any of the problems noted above, or any numberof errors can be thrown with the RAISERROR statement in stored procedures.

Using ADO.NET and the SQL Server managed provider, theseerrors bubble up to your code in the form of a SqlException that gets thrownfrom the execution of a command, which you should naturally catch and handleappropriately. However, there is a lot of embedded information in aSqlException that many developers fail to take advantage of. The typicalapproach used to handle the exception involves writing out the error message ofthe exception to an event log or some other reporting mechanism, possibly withthe exception's embedded context information about the location that the exceptionwas thrown within the .NET code. But often overlooked is the contextinformation about what happened down in the bowels of the database where thereal error occurred, which can be extremely helpful in debugging andtroubleshooting data access problems. Stored procedures can call other storedprocedures, 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 didraise the exception.

SqlException Reveals All

The SqlException class has many properties that exposeadditional context information about the first error that was generated inexecuting a command. These properties include the Procedure, LineNumber, State,Class, and Number of the error that correspond to the individual parts of anerror at the SQL Server level. However, these properties do not always give youthe complete picture of what happened within the context of the call becausethey only tell you about the first error that occurred. Usually this is theroot cause and the first thing you need to address, but there could beadditional errors or warnings thrown that add understanding to what really wentwrong during the execution of the query.

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

Figure 1. SqlErrorproperties give you a better understanding of all the information relative toan error or warning that occurred during the execution of a query.

By looping through the Errors collection on anySqlException thrown from your data access code, and outputting the propertiesof each SqlError object in that collection, you will have a lot betterdebugging and troubleshooting information available.

In the download code accompanying this article, I haveincluded a simple client application that you can use to experiment with thisapproach. It allows you to enter a connection string and a query string andexecute the query. The code captures any SqlException thrown, copies the Errorscollection into an ArrayList, and data binds the ArrayList to a DataGrid fordisplay (see Figure 2). The ArrayList is needed for the sample because althoughthe SqlErrorCollection implements the IEnumerable and ICollection interfacesfor easy iteration, it does not implement the IList interface, which isrequired 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 = newArrayList(sqlEx.Errors);

  dgErrors.DataSource =al;

}

catch (Exception ex)

{

  MessageBox.Show(ex.Message);

}

finally

{

  conn.Close();

}

Figure 2. When youcatch a SqlException, you should process the Errors collection on the exceptionobject to obtain full information on all the errors and warnings that werethrown within the scope of the command execution.

The download code also contains a SQL script that you canuse to add some dummy stored procedures to one of your databases to see howthis works when multiple errors and warnings are being thrown. Figure 3 showsthe sample program after having called those stored procedures to generatemultiple errors.

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

Store the Info

Of course, in a real-world application, you are probablynot going to have a DataGrid lying around to data bind the collection of errorsinto, so you will want to put them out somewhere else so you can use them fortroubleshooting and debugging. I highly recommend you look into the ExceptionManagement 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 theEMAB basically lets you easily publish exception information from any of yourcatch blocks with a single line of code, and allows you to output the exceptioninformation to a configurable number of publishers with no changes to yourapplication 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 sampleproject that uses the Exception Management Application Block and a custompublisher for SqlExceptions to output the SqlError information to an XmlFile,and is available for download.

Brian Noyes is aconsultant, trainer, speaker, and writer with IDesign, Inc. (http://www.idesign.net), a .NET-focusedarchitecture and design consulting firm. Brian specializes in designing andbuilding data-driven distributed applications. He has over 12 years experiencein programming, engineering, and project management, and is a contributingeditor for asp.netPROand other publications. Contact him at mailto:brian.noyes@idesign.net.