Retrieving error code from soap exception

I have published a stored procedure as a web service using SQLXML. When an
error occurs in the stored procedure a SOAP exception is thrown. Is there a
convenient way to retrieve the SQL Server error code from the SOAP
exception?
The only way I can see of getting it is by running an xpath statement on the
XmlNode that gets returned by the SOAP exception. This is rather clunky. Any
ideas on how to do it better?
This is the exception XML:
- <SOAP-ENV:Envelope xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:sqltypes="http://schemas.microsoft.com/SQLServer/2001/12/SOAP/types"
xmlns:sqlmessage="http://schemas.microsoft.com/SQLServer/2001/12/SOAP/types/
SqlMessage"
xmlns:sqlresultstream="http://schemas.microsoft.com/SQLServer/2001/12/SOAP/t
ypes/SqlResultStream">
- <SOAP-ENV:Body>
- <SOAP-ENV:Fault>
<faultcode>SOAP-ENV:Client</faultcode>
<faultstring>Runtime errors.</faultstring>
- <detail>
- <sqlresultstream:SqlMessage xsi:type="sqlmessage:SqlMessage"
sqltypes:IsNested="false">
<sqlmessage:Class>0</sqlmessage:Class>
<sqlmessage:LineNumber>6</sqlmessage:LineNumber>
<sqlmessage:Message>The statement has been
terminated.</sqlmessage:Message>
<sqlmessage:Number>3621</sqlmessage:Number>
<sqlmessage:Procedure> SetupPackingProcess_CreatePackingProcess
</sqlmessage:P
rocedure>
<sqlmessage:Server>THINKSCAPE</sqlmessage:Server>
<sqlmessage:Source>Microsoft OLE DB Provider for SQL
Server</sqlmessage:Source>
<sqlmessage:State>1</sqlmessage:State>
</sqlresultstream:SqlMessage>
- <sqlresultstream:SqlMessage xsi:type="sqlmessage:SqlMessage"
sqltypes:IsNested="false">
<sqlmessage:Class>14</sqlmessage:Class>
<sqlmessage:LineNumber>6</sqlmessage:LineNumber>
<sqlmessage:Message>Cannot insert duplicate key row in object
'PackingProcess' with unique index 'PackingProcess_b'.</sqlmessage:Message>
<sqlmessage:Number>2601</sqlmessage:Number>
<sqlmessage:Procedure> SetupPackingProcess_CreatePackingProcess
</sqlmessage:P
rocedure>
<sqlmessage:Server>THINKSCAPE</sqlmessage:Server>
<sqlmessage:Source>Microsoft OLE DB Provider for SQL
Server</sqlmessage:Source>
<sqlmessage:State>3</sqlmessage:State>
</sqlresultstream:SqlMessage>
</detail>
</SOAP-ENV:Fault>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
McGy
[url]http://mcgy.blogspot.com[/url]Since the result is an XML instance, you will have to check in the SOAP
package with an XPath expression.
Where are you looking for the error? On the server or mid-tier?
Could you use the SOAP toolkit to inspect for errors?
Thanks
Michael
"McGy" <anon@.anon.com> wrote in message
news:ed0$jBXuFHA.596@.TK2MSFTNGP12.phx.gbl...
>I have published a stored procedure as a web service using SQLXML. When an
> error occurs in the stored procedure a SOAP exception is thrown. Is there
> a
> convenient way to retrieve the SQL Server error code from the SOAP
> exception?
> The only way I can see of getting it is by running an xpath statement on
> the
> XmlNode that gets returned by the SOAP exception. This is rather clunky.
> Any
> ideas on how to do it better?
> This is the exception XML:
> - <SOAP-ENV:Envelope xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
> xmlns:sqltypes="http://schemas.microsoft.com/SQLServer/2001/12/SOAP/types"
> xmlns:sqlmessage="http://schemas.microsoft.com/SQLServer/2001/12/SOAP/type
s/
> SqlMessage"
> xmlns:sqlresultstream="http://schemas.microsoft.com/SQLServer/2001/12/SOAP
/t
> ypes/SqlResultStream">
> - <SOAP-ENV:Body>
> - <SOAP-ENV:Fault>
> <faultcode>SOAP-ENV:Client</faultcode>
> <faultstring>Runtime errors.</faultstring>
> - <detail>
> - <sqlresultstream:SqlMessage xsi:type="sqlmessage:SqlMessage"
> sqltypes:IsNested="false">
> <sqlmessage:Class>0</sqlmessage:Class>
> <sqlmessage:LineNumber>6</sqlmessage:LineNumber>
> <sqlmessage:Message>The statement has been
> terminated.</sqlmessage:Message>
> <sqlmessage:Number>3621</sqlmessage:Number>
> <sqlmessage:Procedure> SetupPackingProcess_CreatePackingProcess
</sqlmessage
:P
> rocedure>
> <sqlmessage:Server>THINKSCAPE</sqlmessage:Server>
> <sqlmessage:Source>Microsoft OLE DB Provider for SQL
> Server</sqlmessage:Source>
> <sqlmessage:State>1</sqlmessage:State>
> </sqlresultstream:SqlMessage>
> - <sqlresultstream:SqlMessage xsi:type="sqlmessage:SqlMessage"
> sqltypes:IsNested="false">
> <sqlmessage:Class>14</sqlmessage:Class>
> <sqlmessage:LineNumber>6</sqlmessage:LineNumber>
> <sqlmessage:Message>Cannot insert duplicate key row in object
> 'PackingProcess' with unique index
> 'PackingProcess_b'.</sqlmessage:Message>
> <sqlmessage:Number>2601</sqlmessage:Number>
> <sqlmessage:Procedure> SetupPackingProcess_CreatePackingProcess
</sqlmessage
:P
> rocedure>
> <sqlmessage:Server>THINKSCAPE</sqlmessage:Server>
> <sqlmessage:Source>Microsoft OLE DB Provider for SQL
> Server</sqlmessage:Source>
> <sqlmessage:State>3</sqlmessage:State>
> </sqlresultstream:SqlMessage>
> </detail>
> </SOAP-ENV:Fault>
> </SOAP-ENV:Body>
> </SOAP-ENV:Envelope>
> --
> McGy
> [url]http://mcgy.blogspot.com[/url]
>
>|||Thanks for getting back.
I am catching the error within a C# ASP.Net application. I am currently
using xpath to check for the SQL error code - its quite ugly though.
You mention the SOAP toolkit to inspect for errors, can that help me in any
way?
McGy
[url]http://mcgy.blogspot.com[/url]
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:epmM1QkvFHA.1392@.tk2msftngp13.phx.gbl...
> Since the result is an XML instance, you will have to check in the SOAP
> package with an XPath expression.
> Where are you looking for the error? On the server or mid-tier?
> Could you use the SOAP toolkit to inspect for errors?
> Thanks
> Michael
> "McGy" <anon@.anon.com> wrote in message
> news:ed0$jBXuFHA.596@.TK2MSFTNGP12.phx.gbl...
>|||I have made some progess on this. I have unchecked "return errors as soap
faults" for my stored procedure. Rather than an exception I now get a
SqlMessage object returned if there is an error.
Am I safe to assume that if the first object returned is of type SqlMessage
then an error has occurred otherwise the soap call has succeeded?
I use this code:
if ( results [ 0 ].GetType ( ) == typeof ( SqlMessage ) )
{
SqlMessage message = ( SqlMessage ) results [ 0 ];
Console.WriteLine ( "An error has occurred: " + message.Number );
}
McGy
[url]http://mcgy.blogspot.com[/url]
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:epmM1QkvFHA.1392@.tk2msftngp13.phx.gbl...
> Since the result is an XML instance, you will have to check in the SOAP
> package with an XPath expression.
> Where are you looking for the error? On the server or mid-tier?
> Could you use the SOAP toolkit to inspect for errors?
> Thanks
> Michael
> "McGy" <anon@.anon.com> wrote in message
> news:ed0$jBXuFHA.596@.TK2MSFTNGP12.phx.gbl...
>