WCF Service returning DataTable with SQLServer 2005 Reporting Serv
Ajit
Platform : .NET Framework 3.0, Visual Studio 2005, SQL Server 2005
We are trying to integrate a WCF service with a SQL Server 2005 Reporting
services. We select XML services as the data source from the Reporting
services. Entered the http://.../myservice.svc as the connection string.
While creating the report, I am querying the service using XML. We are
passing a DateTime parameter as input to the service. The WCF Service returns
a DataTable from a DataSet. The XPATH query is shown below :
<Query>
<Method Name="GetWeeklyRedemption" Namespace="http://tempuri.org/">
<Parameters>
<Parameter Name="fromDate">ABC</Parameter>
</Parameters>
</Method>
<SoapAction>http://tempuri.org/ITransactionProcessing/GetWeeklyRedemption</SoapAction>
<ElementPath IgnoreNamespaces="true">NewDataSet/WeeklyRedemption</ElementPath>
</Query>
This query doesn’t return any values. But setting
<ElementPath IgnoreNamespaces="true">*</ElementPath>, it retuns error.
Included below is the XML data returned by the WCF Service.
<s:Body>
<GetWeeklyRedemptionResponse xmlns="http://tempuri.org/">
<GetWeeklyRedemptionResult>
<xs:schema id="NewDataSet" xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns="" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true"
msdata:MainDataTable="WeeklyRedemption" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="WeeklyRedemption">
<xs:complexType>
<xs:sequence>
<xs:element name="Account">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="20"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="FundCode">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="10"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<diffgr:diffgram
xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<DocumentElement xmlns="">
<WeeklyRedemption diffgr:id="WeeklyRedemption1" msdata:rowOrder="0"
diffgr:hasChanges="inserted">
<Account>186486</Account>
<FundCode>370</FundCode>
</WeeklyRedemption>
<WeeklyRedemption diffgr:id="WeeklyRedemption2" msdata:rowOrder="1"
diffgr:hasChanges="inserted">
<Account>186486</Account>
<FundCode>370</FundCode>
</WeeklyRedemption>
</DocumentElement>
</diffgr:diffgram>
</GetWeeklyRedemptionResult>
</GetWeeklyRedemptionResponse>
</s:Body>
MSDN seems to have poor documentation on how to integrate WCF services with
SQL Server 2005 Reporting Services.
Do you think this is an issue with not creating the DataTable ( from DataSet
) ?
Any other thoughts ?
With Regards
Ajit