SQL Server 2005 Reporting Serivces: XML Datasource problem

Hi,

I'm just starting to use SQL Server 2005 Reporting services, and need to use an XMLdocument as the datasource. I will get the xml data from a webservice and then need to bind it to the report. Here is what I am doing:

1. I created a webservice at the following location:
http://localhost/myWebDirectory/myService.asmx. this webservice has a method called "GetXMLDocument" that has 1 parameter (sql). This method uses the sql statement to fill a dataset from a SQL Server database, converts it to an xmlDocument, and returns the xmlDocument. Here is a sample of the results:

< xml version="1.0" encoding="utf-8" >
- <NewDataSet>
- <Table>
<ReportName>GirlsNamedMary</ReportName>
<ReportFriendlyName>All Girls Named Mary</ReportFriendlyName>
<ReportDescription>This is a test SQL Server Reporting Services report. It has no parameters.</ReportDescription>
</Table>
</NewDataSet>

2. I created a datasource in my reports project as follows:
- Name: TestDataSource
- Type: XML
- Connection string: http://localhost/myWebDirectory/myService.asmx

3. I created the following dataset:
- Name: TestDataSet
- Data source: TestDataSource
- Query string:
<Query xmlns="http://tempuri.org/Service/GetXMLDocument">
<Method Namespace="http://tempuri.org/Service" Name="GetXMLDocument"/>
<ElementPath />
</Query>

I am getting this error:
------------------------------------------------------------------------------------
An error occurred while executing the query. Failed to execute web request for the specified URL.

Additional Information:
----> Failed to execute web request for the specified URL. (Microsoft.ReportingServices.DataExtensions).
------------------------------------------------------------------------------------

I cannot figure out why I have this error. The service runs fine from the browser, but I just can't get it to work in Reporting Services. What am I doing wrong

Answer this question

SQL Server 2005 Reporting Serivces: XML Datasource problem

Robert McIntyre - MSFT
I am trying to create a report where one of the datasets uses a webservice as a datasource. This webservice is on our intranet and am able to access it via the browser. The datasource connection string is set to webservice's URL with credentials using integrated security (I have tried no credentials too but didn't work) when I try to execute (!) my dataset query I get "An error occurred while executing the query.Failed to execute web request for the specified URL." Additional Information on this is:
------------------------------
< xml version="1.0" encoding="utf-8" >
<soap:Envelope xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<soap:Fault>
<faultcode>soap:Client</faultcode>
<faultstring>System.Web.Services.Protocols.SoapException: Server did not recognize the value of HTTP Header SOAPAction: http://njrdbptl91/PortalUserReturn/PortalUserInfo.
at System.Web.Services.Protocols.Soap11ServerProtocolHelper.RouteRequest()
at System.Web.Services.Protocols.SoapServerProtocol.Initialize()
at System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type, HttpContext context, HttpRequest request, HttpResponse response, Boolean& abortProcessing)</faultstring>
<detail />
</soap:Fault>
</soap:Body>
</soap:Envelope>
------------------------------

The query that I am using is :

<Query>
<SoapAction>http://njrdbptl91/PortalUserReturn/PortalUserInfo</SoapAction>

<Method Name="PortalUserInfo" Namespace="http://njrdbptl91/PortalUserReturn/">

<Parameters>

<Parameter Name="strUserEmail">

<DefaultValue>vmehta@khov.com</DefaultValue>

</Parameter>

<Parameter Name="strField1">

<DefaultValue>Title</DefaultValue>

</Parameter>

</Parameters>

</Method><ElementPath IgnoreNameSpaces="true">*</ElementPath>
</Query>

As far as I know the webservice was moved from the developers machine to the production one so the namespace has not been changed.

I have been trying to resolve this since last 4 days but nothing has worked. Please help me on this.
ss23
Thanks for the reply Ian, now I am using web service name space mentioned in the WSDL and it is http://njrdbptl91/PortalUserReturn/ (with / at the end of it) hence my SoapAction is http://njrdbptl91/PortalUserReturn/Portaluserinfo (portaluserinfo is the method name). I have followed all the recommendations in your posts but still I get the error.
dennist

Glad to hear you're winning!

i think to get back multiple columns you'll need to make use of the element path -- check books online for that.

my webservice only returns a single value and then the xml note with it...it's what's defined in the soap message when you browse to the service in a web-browser.
Rupesh Rao MSFT
Yes, RS should pass any regular query parameters in the SOAP request. You can also specify the parameters directly in the query. Also, parameters specified directly in the query are overridden by the values of the regular dataset query parameters, if there are any.

Here is an example of calling a web method that requires two parameters, a query string and a database name, and returns the resultant dataset.

<Query>

<SoapAction>http://tempuri.org/RunQuery</SoapAction>
<Method Namespace="http://tempuri.org/" Name="RunQuery">
<Parameters>
<Parameter Name="query" Type="String">
<DefaultValue>Select * From Sales.Customer</DefaultValue>
</Parameter>
<Parameter Name="database" Type="String">
<DefaultValue>AdventureWorks</DefaultValue>
</Parameter>
</Parameters>
</Method> <ElementPath>RunQueryResponse{}/RunQueryResult{}/diffgram{}/DataSetName/TableName</xmldp:ElementPath>
</Query>
HariValath
In IIS I had the authentication methods for my web service set to both Anonymous access and integrated Windows Authentication. I unchecked Integrated Windows authentication and still get the same error.
Joel Holdsworth
Well, I did make a couple of changes which partially help. I cleared out the connection string, and set the the credentials to "no credentials". Then in the query string I put in the following test XML:

<Query><XmlData> <NewDataSet>
<Table>
<FirstName>Mary</FirstName>
<MiddleName>B.</MiddleName>
<LastName>Billstrom</LastName>
<EmailAddress>mary3@adventure-works.com</EmailAddress>
<Phone>1 (11) 500 555-0136</Phone>
</Table>
<Table>
<FirstName>Mary</FirstName>
<LastName>Bishop</LastName>
<EmailAddress>mary4@adventure-works.com</EmailAddress>
<Phone>176-555-0136</Phone>
</Table>

</NewDataSet></XmlData></Query>

That works great in preview mode, but when I try to view the report in a browser, I get the following error:
- The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. (rsInvalidDataSourceCredentialSetting)
I should not get that kind of error since I am using XML data instead of making a connection to any external data source at all. I also get the same error if I change the credentials to Windows Authentication (Integrated Security).

Any ideas
PrakashV

I've gotten that when my datasource didn't point to the .asmx file for the webservice...

But since yours is already set to http://localhost/myWebDirectory/myService.asmx , I'm not sure that's the problem. I'd definitely keep playing with the dataset though...if you hit the red exclamation point, does it come back with anything other than the error saying the url didn't work Is there any security on the webservice in IIS, set to logon anon etc
Jo&#227;o Marcelino
Thank you guys sooo much! The report seems to be working perfectly now! Now I am going to work on integrating this w/ my .Net app. Thanks again for all your help!
Niaz

This error means that the web service specified in the SOAPAction does exist on the server. This is usually caused by the web service being moved to another machine, without having its namespace updated, and clients accessing it with a namespace that includes the new machine name. The SOAPAction should use the namespace of the web service, regardless of the machine on which it exists.

When you access the service via the browser, what is the namespace specified for the web service

Ian
ikeeickholdt
OK, I am making some progress. My <SoapAction> tag was using the url of the web service and not it's namespace. I changed it, so it looks like this:

<Query>
<SoapAction>http://tempuri.org/GetXML</SoapAction>
<ElementPath />
</Query>

(note I have also changed the method name while mucking about.)

Now I am not getting any errors, but I only get back 2 columns: GetXMLResult and xmlns. GetXMLResult holds a value of <NewDataSet>, but I don't see the data. I must be close!!!!
Sharkboy
I tried that, but am getting the following error:

Could not generate a list of fields for the query. Check the query syntax, or click Refresh Fields on the query toolbar.
Additional Information
---> Failed to execute web request for the specified URL (Microsoft.ReportingServices.Designer)
Yanbing Hu

Have you tried to change the dataset to something like:

<query>
<SoapAction>http://localhost/myWebDirectory/myService/GetXMLDocument</SoapAction>
<ElementPath />
</query>

The soap action should match what you see in your browser when examining your web service.

HTH.
BLueDeViL
Joe is correct; you need to set the element path so that the XmlDP extension knows the schema to use to retrieve the data from the Xml document. Otherwise it traverses the first heierachy it finds and creates the schema based on this. In your case, for the SOAP response you have described, you should use the following element path.

<ElementPath IgnoreNamespaces="true">GetXMLResponse{}/GetXMLResult{}/NewDataSet/Table</ElementPath>

If you did not convert the dataset on your own to xml and just returned the dataset from the web method, then would use the following.

<ElementPath IgnoreNamespaces="true">GetXMLResponse{}/GetXMLResult{}/diffgram{}/DataSetName/TableName</ElementPath>

It is also a good idea to specify the types of the fields in the element path. Otherwise they will always be interpreted as strings. See the links below for more details on this.

Here are some helpful links:

Element Path Syntax: http://msdn2.microsoft.com/en-US/library/ms365158.aspx
Russell Christopher's Blog: http://blogs.msdn.com/bimusings/archive/2006/03/24/560026.aspx
Ed Teach
OK! I have been studying your references and working on this for a couple of hours, and still (!) couldn't make it work, and it finally occurred to me to get rid of my parameter ( a dynamic sql statement for the dataset ), and now it works!!! Thanks!!!

I wrote a new webmethod that does NOT take a sql statement, but uses a hard-coded one. Then I got rid of the parameter in the datasource, and voila! So now I am wondering, can you not pass parameters into your webmethod from the SQL Server report
SQL Server 2005 Reporting Serivces: XML Datasource problem

Answer this question