I'm finally back, well more like I finally found some time to blog.

As promised, I will add a little introduction to how Microsoft SQL 2005 native web service support works.

Native web service support is new to Microsoft SQL 2005.  In SQL 2000, web service support was done basically through the use of ADO.Net and IIS.  With SQL 2005, the SOAP, XML, and XSD schema support have all been brought into the server.  Of course, the SQL 2000 style of hosting a web service is still available and in some cases even preferred over the new SQL 2005 style.

Basic system requirements for native web service support in SQL 2005 is Windows 2003 server or Windows XP SP2.  This is because these two OS platforms contains the HTTP.sys resource that is needed.  This also means that SQL 2005 native web service support does not require IIS.

So, onwards to the topic at hand.  The native web service support in SQL 2005 provides the ability to expose any SP or Function as a web service method.  This includes CLR SP and CLR Functions.  In addition, a special built-in method, called "sqlbatch", can be enabled to allow for adhoc queries.  All of this is done through what is typically referred to as ENDPOINT mapping.  The following is a sample borrowed from Books Online:

CREATE ENDPOINT sql_endpoint
STATE = STARTED
AS HTTP(
   PATH = '/sql',
   AUTHENTICATION = (INTEGRATED ),
   PORTS = ( CLEAR ),
   SITE = '*'
   )
FOR SOAP (
   WEBMETHOD 'http://tempUri.org/'.'GetCustomerInfo'
            (name='AdventureWorks.dbo.GetCustomerInfo',
             schema=STANDARD ),
   WEBMETHOD 'UDFReturningAScalar'
            (name='AdventureWorks.dbo.UDFReturningAScalar'),
   BATCHES = ENABLED,
   WSDL = DEFAULT,
   DATABASE = 'AdventureWorks',
   NAMESPACE = 'http://AdventureWorks/Customer'
   )
GO

In this above sample, the special "sqlbatch" adhoc query method is enabled by the 'BATCHES = ENABLED' setting and 1 SP "GetCustomerInfo" and 1 UDF "UDFReturningAScalar" is exposed on the endpoint.  The endpoint URL is 'http://machinename/sql'.  Also, something I haven't mentioned is that each endpoint can enable or disable WSDL document generation.  In the above case, WSDL generation is enabled by the 'WSDL = DEFAULT' setting.  The reason that the keyword 'DEFAULT' is used is that you are actually allowed to create your own WSDL generation SP.  In the rare case where you actually want to use your WSDL generation SP, the setting would look more like "WSDL = 'db.owner.myWsdlSp'".

Also, as a quick note, SQL 2005 native web service support follows the guidline set out by the WS-I Basic Profile 1.0 specification.  Specifically, it only supports DOC/LIT formatting and must use POST to send the SOAP request.

Oh, one more thing about the default WSDL generation, typically to get the WSDL document from an URL a HTTP GET request is issued to the URL with the query string of ?wsdl.  So, for the above sample, the WSDL GET would be against http://servername/sql?wsdl.  With SQL 2005, the default WSDL generator is actually able to generate 2 WSDL documents.  The default one, when ?wsdl is received, will use fully decorated XSD schema when declaring parameter and return types.  This is so that the uniqueness of SQL data types is defined and identifiable by the client.  The second version, when ?wsdlsimple is received, will use standard XSD data types to represent method parameters and return types.  This is so that older generation SOAP clients can still communicate with SQL 2005.

I'll talk about permissions on my next blog.

Jimmy