SQL Server 2005 Reporting Serivces: XML Datasource problem

SQL Server 2005 Reporting Serivces: XML Datasource problem

• diling00

  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
  
  
  
  
  

• RedKMan

  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)

  
  

• Carlos Lenz

  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

   

  
  
  

• Kazuya Ujihara

  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.

  
  

• Richard Arnoldx

  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!
  

• Drasko

  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&amp; 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.
  
  

• byroman

  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!!!!

  
  

• Wytze

  
  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
  
  
  

• Nuno31415

  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

  
  

• Marcelo Jorge

  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.
  

• Matt84

  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

  
  

• Daniel M&amp;#228;chler

  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.

  
  
  

• photoval

  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.

  
  
  

• MarAng

  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>
  <!-- Specify both the SoapAction and Method elements when the webservice namesapace ends in a '/' -->
  <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>
  
  
  
  
  
• SQL Server 2005 Reporting Serivces: XML Datasource problem