Valid SELECT statement resulting in empty DataSet

Valid SELECT statement resulting in empty DataSet

  • Comments 6

When using a Visual Studio 2005 application to retrieve a SELECT statement resultset through SQL 2005 native web services, the resultset is de-serialized from the wire format into a DataSet object. It is possible that even though the resultset is completely valid (ie. has columns and rows), the DataSet object is still empty. This scenario occurs when the web method called is not configured to return the XSD schema for the resultset.
For example, the endpoint was configured as:

 CREATE ENDPOINT sql_endpoint 
 STATE = STARTED
 AS HTTP(
    PATH = '/sql',
    AUTHENTICATION = (INTEGRATED ),
    PORTS = ( CLEAR ),
    SITE = 'SERVER'
    )
 FOR SOAP (
    WEBMETHOD 'foobar'
             (name='master.dbo.sp_foobar'),
    WSDL = DEFAULT,
    SCHEMA=NONE,
    DATABASE = 'master',
    NAMESPACE = 'http://tempUri.org/'
    );
Or
 CREATE ENDPOINT sql_endpoint
 STATE = STARTED
 AS HTTP(
    PATH = '/sql',
    AUTHENTICATION = (INTEGRATED ),
    PORTS = ( CLEAR ),
    SITE = 'SERVER'
    )
 FOR SOAP (
    WEBMETHOD 'foobar'
             (name='master.dbo.sp_foobar', SCHEMA=NONE),
    WSDL = DEFAULT,
    DATABASE = 'master',
    NAMESPACE = 'http://tempUri.org/'
    );

The reason behind the empty DataSet is because when the XSD schema for the resultset is not returned in the response, the DataSet object has no mechanism to determine what the table structure is (ie. how many columns are there and what the data type is for each of the columns).

This problem can be resolved by either updating the webmethod (recommended) keyword "SCHEMA" to "STANDARD" or, updating the "FOR SOAP" section keyword "SCHEMA" to "STANDARD".

 ALTER ENDPOINT sql_endpoint
 FOR SOAP
 (
   ALTER WEBMETHOD 'foobar' (name='myDatabase.dbo.sp_foobar', SCHEMA=STANDARD)
 );
or
 ALTER ENDPOINT sql_endpoint
 FOR SOAP
 (
   SCHEMA=STANDARD
 );

Please note that by default the SCHEMA keyword within the WEBMETHOD section is set to "DEFAULT", which means it defers to the SCHEMA setting for the SOAP endpoint. The default setting for the SOAP endpoint when the SCHEMA keyword is not specified within the FOR SOAP section is STANDARD. So, the scenario where this problem occurs would exist only if some one explicitly specified "NONE" for the SCHEMA keyword.

Please refer to CREATE ENDPOINT and ALTER ENDPOINT in Books Online for complete details.

Jimmy Wu, SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Leave a Comment
  • Please add 6 and 3 and type the answer here:
  • Post
  • I get an 'Error in the XML document' when I try and use anything other than a scalar function. Any ideas? SQL 2005 seems to allow any type of endpoint creation on views or tables, but neither of these are supported.
  • Dylan,
    For SOAP endpoints, SQL 2005 does not allow for endpoint creation on views or tables. If you know of a reference that mentions this capability, please let me know so it can be corrected.
    Regarding to the 'Error in the XML document' error you are seeing, my first guess would be that you are trying to connect with SQL 2005 using a Visual Studio 2003 compiled application. You are most likely getting something similar to:
    System.InvalidOperationException
    There is an error in XML document (1, 6652).
    at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)
    at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, XmlDeserializationEvents events)
    at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader)
    at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
    at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
    This error occurs when using an application compiled with Visual Studio 2003 because the XSD schema generated by SQL 2005 is not fully supported by Visual Studio 2003. Our recommendation is to write and compile the client application with Visual Studio 2005. Your existing Visual Studio 2003 code can be recompiled using .Net Frameworks 2.0 (which is installed with SQL 2005, usually under %systemroot%\Microsoft.NET\Framework directory). If us know if that solves your problem.
  • I have a Web Service that return a DataSet and I like to know inside my app how to detect that my dataset is empty or no.
  • One possible way of detecting whether the dataset is empty or not is to check the DataSet.Tables.Count property. If the number of DataTables in the DataTableCollection is 0, then the DataSet is empty.

    Jimmy Wu, SQL Server Protocols
    Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights
  • Great Info. We are getting the exact exception you describe in your response to Dylan's problem. Is there any other work around besides upgrading the app to 2005?

    Thanks.
  • John,
    If upgrading the client application to Visual Studio 2005 is not a possibility, then one solution would be to change the WSDL document generated by the SQL server. One way to achieve this is to use the custom WSDL generator (see http://msdn2.microsoft.com/ms188321.aspx). Another possibility is to retrieve the WSDL document using IE (or another web browser) and save the document as a file on the local hard drive. Then edit the document looking for
    <xsd:complexType name="SqlRowSet">
    <xsd:sequence maxOccurs="unbounded">
    <xsd:element ref="xsd:schema" />
    <xsd:any />
    </xsd:sequence>
    </xsd:complexType>
    Change the above section to
    <xsd:complexType name="SqlRowSet">
    <xsd:sequence maxOccurs="unbounded">
    <xsd:any maxOccurs="unbounded" minOccurs="0" processContents="lax" />
    </xsd:sequence>
    </xsd:complexType>
    Then in Visual Studio, when adding a web reference, point to the file as the source of the WSDL document. What should happen is that Visual Studio 2003 will treat what would have been the Dataset as an array of XML nodes. It would be up to the client application to parse through the different XML nodes to retrieve the necessary information.

    Jimmy Wu, SQL Server Protocols
    Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights.
Page 1 of 1 (6 items)