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_CreatePa ckingProcess</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_CreatePa ckingProcess</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>
McGeeky
http://mcgeeky.blogspot.com
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
"McGeeky" <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/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_CreatePa ckingProcess</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_CreatePa ckingProcess</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>
> --
> McGeeky
> http://mcgeeky.blogspot.com
>
>
|||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?
McGeeky
http://mcgeeky.blogspot.com
"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
> "McGeeky" <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 );
}
McGeeky
http://mcgeeky.blogspot.com
"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
> "McGeeky" <anon@.anon.com> wrote in message
> news:ed0$jBXuFHA.596@.TK2MSFTNGP12.phx.gbl...
>
Showing posts with label service. Show all posts
Showing posts with label service. Show all posts
Tuesday, March 20, 2012
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>
McG
y
[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...
>
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>
McG
y[url]http://mcg
y.blogspot.com[/url]Since the result is an XML instance, you will have to check in the SOAPpackage 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
"McG
y" <anon@.anon.com> wrote in messagenews: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>
> --
> McG
y> [url]http://mcg
y.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?
McG
y[url]http://mcg
y.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
> "McG
y" <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 );
}
McG
y[url]http://mcg
y.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
> "McG
y" <anon@.anon.com> wrote in message> news:ed0$jBXuFHA.596@.TK2MSFTNGP12.phx.gbl...
>
Friday, March 9, 2012
Retrieving a list of Event Classes
Hi,
I have been trying in vain for the last few hours to retrieve a list of EventClasses for a particular Notification Service instance. Its a web application that I'm building I want to be able to populate a drop down list with all the notifications a user can subscribe to. Any advice on the subject would be greatly appreciated.
RegardsIn relation to the above its actually a list of subscription classes that I wish to retrieve from the notification services instance and then the user will be able to select which one they wish to subscribe to. Sorry about the mix up.
Regards|||Hi,
I have been trying in vain for the last few hours to retrieve a list of EventClasses for a particular Notification Service instance. Its a web application that I'm building I want to be able to populate a drop down list with all the notifications a user can subscribe to. Any advice on the subject would be greatly appreciated.
RegardsIn relation to the above its actually a list of subscription classes that I wish to retrieve from the notification services instance and then the user will be able to select which one they wish to subscribe to. Sorry about the mix up.
Regards|||Hi,
Stored procedure named "NSGetSubscriptionClasses" on NS application database will get you that information.
Shamir
Wednesday, March 7, 2012
Retrieve URL for ReportingServices Web Service.
Hi
I need to be able to retrieve the URL for the ReportingServices Web Service
programmatically. Is this possible?
I am currently updating a reporting services deployment tool for SQL 2005
and 2008, in my old version (SQL Server 2003) the Web Service was almost
always guaranteed to be http://<server>/ReportServer/ReportService.asmx,
however with the latest version of SQL and Reporting Services Configuration
Manager this is not always be the case.
I have the sql server name that I want to deploy to but I can see no way of
getting the URL to the ReportingServices Web Service to do the deployment.
Any ideas?On Jan 7, 10:09 am, Busby <Bu...@.discussions.microsoft.com> wrote:
> Hi
> I need to be able to retrieve the URL for the ReportingServices Web Service
> programmatically. Is this possible?
> I am currently updating a reporting services deployment tool for SQL 2005
> and 2008, in my old version (SQL Server 2003) the Web Service was almost
> always guaranteed to be http://<server>/ReportServer/ReportService.asmx,
> however with the latest version of SQL and Reporting Services Configuration
> Manager this is not always be the case.
> I have the sql server name that I want to deploy to but I can see no way of
> getting the URL to the ReportingServices Web Service to do the deployment.
> Any ideas?
This is just a thought, but you could try adding a web reference to a
custom ASP.NET application for the report server and check that
reference path. Normally, the URL path for SSRS 2005 is here:
http://myserver/reportserver/ReportExecution2005.asmx
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi Enrique
That would work if I knew which server I want to deploy to, however my
application allows the users to deploy to multiple servers.
I initially query instances of SQL server on the network, I then allow the
users to select a number of reports which I will then create on the report
server. Finally I will change each deployed reports data source to point to
the selected sql server.
This worked fine for SQL 2000 where the web service for reporting services
was static, however for 2005 and 2008 the web services url can change
depending on how its configured via the Reporting Services Configuration
utility.
I think the only way I can do this is to query IIS to get a list of web
sites/services and check that the url ends with ReportService.asmx.
Anyone agree? Or have better solution?
"EMartinez" wrote:
> On Jan 7, 10:09 am, Busby <Bu...@.discussions.microsoft.com> wrote:
> > Hi
> >
> > I need to be able to retrieve the URL for the ReportingServices Web Service
> > programmatically. Is this possible?
> >
> > I am currently updating a reporting services deployment tool for SQL 2005
> > and 2008, in my old version (SQL Server 2003) the Web Service was almost
> > always guaranteed to be http://<server>/ReportServer/ReportService.asmx,
> > however with the latest version of SQL and Reporting Services Configuration
> > Manager this is not always be the case.
> >
> > I have the sql server name that I want to deploy to but I can see no way of
> > getting the URL to the ReportingServices Web Service to do the deployment.
> >
> > Any ideas?
>
> This is just a thought, but you could try adding a web reference to a
> custom ASP.NET application for the report server and check that
> reference path. Normally, the URL path for SSRS 2005 is here:
> http://myserver/reportserver/ReportExecution2005.asmx
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Jan 8, 2:55 am, Busby <Bu...@.discussions.microsoft.com> wrote:
> Hi Enrique
> That would work if I knew which server I want to deploy to, however my
> application allows the users to deploy to multiple servers.
> I initially query instances of SQL server on the network, I then allow the
> users to select a number of reports which I will then create on the report
> server. Finally I will change each deployed reports data source to point to
> the selected sql server.
> This worked fine for SQL 2000 where the web service for reporting services
> was static, however for 2005 and 2008 the web services url can change
> depending on how its configured via the Reporting Services Configuration
> utility.
> I think the only way I can do this is to query IIS to get a list of web
> sites/services and check that the url ends with ReportService.asmx.
> Anyone agree? Or have better solution?
> "EMartinez" wrote:
> > On Jan 7, 10:09 am, Busby <Bu...@.discussions.microsoft.com> wrote:
> > > Hi
> > > I need to be able to retrieve the URL for the ReportingServices Web Service
> > > programmatically. Is this possible?
> > > I am currently updating a reporting services deployment tool for SQL 2005
> > > and 2008, in my old version (SQL Server 2003) the Web Service was almost
> > > always guaranteed to be http://<server>/ReportServer/ReportService.asmx,
> > > however with the latest version of SQL and Reporting Services Configuration
> > > Manager this is not always be the case.
> > > I have the sql server name that I want to deploy to but I can see no way of
> > > getting the URL to the ReportingServices Web Service to do the deployment.
> > > Any ideas?
> > This is just a thought, but you could try adding a web reference to a
> > custom ASP.NET application for the report server and check that
> > reference path. Normally, the URL path for SSRS 2005 is here:
> >http://myserver/reportserver/ReportExecution2005.asmx
> > Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
That sounds like the most direct approach. Otherwise, you might need
to design some sort of RS Web Service WSDL discovery application that
dynamically polls the servers with typical URL patterns. Sorry that I
could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant
I need to be able to retrieve the URL for the ReportingServices Web Service
programmatically. Is this possible?
I am currently updating a reporting services deployment tool for SQL 2005
and 2008, in my old version (SQL Server 2003) the Web Service was almost
always guaranteed to be http://<server>/ReportServer/ReportService.asmx,
however with the latest version of SQL and Reporting Services Configuration
Manager this is not always be the case.
I have the sql server name that I want to deploy to but I can see no way of
getting the URL to the ReportingServices Web Service to do the deployment.
Any ideas?On Jan 7, 10:09 am, Busby <Bu...@.discussions.microsoft.com> wrote:
> Hi
> I need to be able to retrieve the URL for the ReportingServices Web Service
> programmatically. Is this possible?
> I am currently updating a reporting services deployment tool for SQL 2005
> and 2008, in my old version (SQL Server 2003) the Web Service was almost
> always guaranteed to be http://<server>/ReportServer/ReportService.asmx,
> however with the latest version of SQL and Reporting Services Configuration
> Manager this is not always be the case.
> I have the sql server name that I want to deploy to but I can see no way of
> getting the URL to the ReportingServices Web Service to do the deployment.
> Any ideas?
This is just a thought, but you could try adding a web reference to a
custom ASP.NET application for the report server and check that
reference path. Normally, the URL path for SSRS 2005 is here:
http://myserver/reportserver/ReportExecution2005.asmx
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi Enrique
That would work if I knew which server I want to deploy to, however my
application allows the users to deploy to multiple servers.
I initially query instances of SQL server on the network, I then allow the
users to select a number of reports which I will then create on the report
server. Finally I will change each deployed reports data source to point to
the selected sql server.
This worked fine for SQL 2000 where the web service for reporting services
was static, however for 2005 and 2008 the web services url can change
depending on how its configured via the Reporting Services Configuration
utility.
I think the only way I can do this is to query IIS to get a list of web
sites/services and check that the url ends with ReportService.asmx.
Anyone agree? Or have better solution?
"EMartinez" wrote:
> On Jan 7, 10:09 am, Busby <Bu...@.discussions.microsoft.com> wrote:
> > Hi
> >
> > I need to be able to retrieve the URL for the ReportingServices Web Service
> > programmatically. Is this possible?
> >
> > I am currently updating a reporting services deployment tool for SQL 2005
> > and 2008, in my old version (SQL Server 2003) the Web Service was almost
> > always guaranteed to be http://<server>/ReportServer/ReportService.asmx,
> > however with the latest version of SQL and Reporting Services Configuration
> > Manager this is not always be the case.
> >
> > I have the sql server name that I want to deploy to but I can see no way of
> > getting the URL to the ReportingServices Web Service to do the deployment.
> >
> > Any ideas?
>
> This is just a thought, but you could try adding a web reference to a
> custom ASP.NET application for the report server and check that
> reference path. Normally, the URL path for SSRS 2005 is here:
> http://myserver/reportserver/ReportExecution2005.asmx
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Jan 8, 2:55 am, Busby <Bu...@.discussions.microsoft.com> wrote:
> Hi Enrique
> That would work if I knew which server I want to deploy to, however my
> application allows the users to deploy to multiple servers.
> I initially query instances of SQL server on the network, I then allow the
> users to select a number of reports which I will then create on the report
> server. Finally I will change each deployed reports data source to point to
> the selected sql server.
> This worked fine for SQL 2000 where the web service for reporting services
> was static, however for 2005 and 2008 the web services url can change
> depending on how its configured via the Reporting Services Configuration
> utility.
> I think the only way I can do this is to query IIS to get a list of web
> sites/services and check that the url ends with ReportService.asmx.
> Anyone agree? Or have better solution?
> "EMartinez" wrote:
> > On Jan 7, 10:09 am, Busby <Bu...@.discussions.microsoft.com> wrote:
> > > Hi
> > > I need to be able to retrieve the URL for the ReportingServices Web Service
> > > programmatically. Is this possible?
> > > I am currently updating a reporting services deployment tool for SQL 2005
> > > and 2008, in my old version (SQL Server 2003) the Web Service was almost
> > > always guaranteed to be http://<server>/ReportServer/ReportService.asmx,
> > > however with the latest version of SQL and Reporting Services Configuration
> > > Manager this is not always be the case.
> > > I have the sql server name that I want to deploy to but I can see no way of
> > > getting the URL to the ReportingServices Web Service to do the deployment.
> > > Any ideas?
> > This is just a thought, but you could try adding a web reference to a
> > custom ASP.NET application for the report server and check that
> > reference path. Normally, the URL path for SSRS 2005 is here:
> >http://myserver/reportserver/ReportExecution2005.asmx
> > Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
That sounds like the most direct approach. Otherwise, you might need
to design some sort of RS Web Service WSDL discovery application that
dynamically polls the servers with typical URL patterns. Sorry that I
could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant
Saturday, February 25, 2012
Retrieve GUID or SID FROM Active Directory via ADSI and T-SQL only?
Hi all,
is there a way to get an object's SID or GUID using T-SQL only?
Up to now I got the following code of Active Directory Service Interfaces working,
but GUID or SID are not among the parameters known to me.
Code Snippet
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'
SELECT * FROM OpenQuery(ADSI, 'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM ''LDAP://DC=whatever,DC=domain,DC=org'' where objectClass = ''User''')
I do not want to use anything other then SQL Server 2000 to get an AD-object's primary key.
Any comments would be appreciated.
Thank you!
Regards,
caracol
Windows 2000 Server, SQL Server 2000, AD in W2K only mode
This is not an SSIS question. Moving to the Transact-SQL Forum.|||Hi all,
just in case there should be someone else looking for identifiable data to be extracted from Active Directory:
A complete list with all attributes can be found at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adschema/adschema/attributes_all.asp
For every attribute the LDAP-Display-Name is given which can be accessed by ADSI.
My SELECT from above should be
Code Snippet
SELECT * FROM OpenQuery(ADSI, 'SELECT objectGUID, title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM ''LDAP://DC=whatever,DC=domain,DC=org'' where objectClass = ''User''')then.
Regards,
caracol
Tuesday, February 21, 2012
Retrieve Dataset using RS Web Service?
I am using the RS web service to generate reports. I know it is possible to
generate lists of available reports, together with a list of required
parameters for each. But is it possible to get a list of valid values for
each parameter via the web service? i.e. retrieve the dataset results that
are used to poulate the filter when the report is viewed using Report
Manager. This would save me writing a query to populate a list in my custom
front end.Dave Morrow wrote:
<snip>
> But is it possible to get a list of valid values for
> each parameter via the web service? i.e. retrieve the dataset results that
> are used to poulate the filter when the report is viewed using Report
> Manager. This would save me writing a query to populate a list in my custom
> front end.
You can check to see if a parameter has ValidValuesQueryBased specified,
and then build a combobox from the ValidValues collection of the
ReportParameter object. I've written a small report launcher in C#
which does this and it works well.
-BA|||<snip>
> and then build a combobox from the ValidValues collection of the
> ReportParameter object.
<snip>
Edit: ValidValues is a property of the ReportParameter class, and is of
type ValidValue[].
-BA|||Thanks for your help, Brian. Much appreciated.
"Brian Almond" wrote:
> <snip>
> > and then build a combobox from the ValidValues collection of the
> > ReportParameter object.
> <snip>
> Edit: ValidValues is a property of the ReportParameter class, and is of
> type ValidValue[].
> -BA
>
generate lists of available reports, together with a list of required
parameters for each. But is it possible to get a list of valid values for
each parameter via the web service? i.e. retrieve the dataset results that
are used to poulate the filter when the report is viewed using Report
Manager. This would save me writing a query to populate a list in my custom
front end.Dave Morrow wrote:
<snip>
> But is it possible to get a list of valid values for
> each parameter via the web service? i.e. retrieve the dataset results that
> are used to poulate the filter when the report is viewed using Report
> Manager. This would save me writing a query to populate a list in my custom
> front end.
You can check to see if a parameter has ValidValuesQueryBased specified,
and then build a combobox from the ValidValues collection of the
ReportParameter object. I've written a small report launcher in C#
which does this and it works well.
-BA|||<snip>
> and then build a combobox from the ValidValues collection of the
> ReportParameter object.
<snip>
Edit: ValidValues is a property of the ReportParameter class, and is of
type ValidValue[].
-BA|||Thanks for your help, Brian. Much appreciated.
"Brian Almond" wrote:
> <snip>
> > and then build a combobox from the ValidValues collection of the
> > ReportParameter object.
> <snip>
> Edit: ValidValues is a property of the ReportParameter class, and is of
> type ValidValue[].
> -BA
>
Retrieve Data from Web Services Using DTS (SQL Server 2000)
The project I'm currently working on involves combining data from one SQL Server 2000 databases and XML returned from a web service into a 3rd SQL Server 2000 database.
This process must be scheduled to happen once a day. If it weren't for the Web Service, I'd say that this is a no-brainer and I'd use DTS. However, I'm not sure if I can even access a Web Service with DTS. Has anyone done this or have any tips?
Id try the microsoft.public.sqlserver.dts forum if I were you.
-Jamie
Subscribe to:
Posts (Atom)