First published on MSDN on Oct 10, 2005
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
