Share via

Valid SELECT statement resulting in empty DataSet

  • Article

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 = 'https://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 = 'https://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

Comments

  • Anonymous
    November 01, 2005
    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.
  • Anonymous
    November 01, 2005
    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.NETFramework directory). If us know if that solves your problem.
  • Anonymous
    November 15, 2005
    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.
  • Anonymous
    November 16, 2005
    The comment has been removed
  • Anonymous
    December 19, 2005
    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.
  • Anonymous
    January 16, 2006
    The comment has been removed