.NET SqlXmlException Class

The managed SQLXML classes throw a SqlXmlException to report any errors that occurred during the execution of a method. This class extends the System.Excpetion class and thus provides all the properties you are used to examine when you catch exceptions in a .NET application.

However, there are some cases where the HResult and the Message property do not provide enough information. Especially errors occurring during the execution of Updategrams often return an ambiguous HResult with the hexadecimal value 0x80040e21 and insufficient information in the Message property. In these cases we have to parse at the SqlXmlException’s  ErrorStream property. The ErrorStream contains the execution results – of course also in XML format where execution errors are reported as processing instructions.

One way to get to the details of the exception is to parse the ErrorStream with an XmlTextReader as outlined in the next code fragment:

try

{

    // do some SQLXML stuff

}

catch( SqlXmlException ex )

{

  XmlTextReader xmlReader = new XmlTextReader( _Ex.ErrorStream );

  while( xmlReader.Read() )

  {

    if( ( XmlNodeType.ProcessingInstruction == xmlReader.NodeType )

      && ( "MSSQLError" == xmlReader.Name ) )

    {

          Console.WriteLine( xmlReader.Value );

    }

  }

}

Of course this example is not very useful because it writes the error to the console. I have taken a more useful approach in an small class you can find on the web site. It parses the ErrorStream property and return all errors in a collection similar to the SqlErrors collection in the System.Data namespace.

TIP: In cases where even the SqlXmlException still does not provide enough details your best bet is to spy on SQLXML with SQL Server’s profiler tool. This tool will show you exactly what queries SQLXML sends to the database. Make sure you run the profiler in a test environment, not in a production environment because it does occupy a fair amount of processing cycles on your database server.