02 April, 2009

Native XML Web Services in SQL Server 2005 - Creating HTTP Endpoints

With SQL Server 2005 developers will be able to develop Web services in the database tier, making SQL Server a hypertext transfer protocol (HTTP) listener and providing a new type of data access capability for Web services-centric applications.

Native XML Web Services in SQL Server 2005

To begin, you need to be running Windows 2003 or Windows XP SP2 to use this feature and you don't need IIS. In fact, if you are running IIS, it may cause a problem if both SQL Server 2005 and IIS are both listening to port 80 when you try to create your HTTP Endpoint. For this tutorial, I would just stop IIS if you have it running. If you don't want to do that, you will need to change the default port that either IIS or SQL Server 2005 is using to listen to HTTP Requests. The default is 80, and one of them will need to be changed.

Expose Stored Procedure as XML Web Service

My goal is pretty simple. I want to expose a stored procedure, called GetContacts, in my AdventureWorks Database as an XML Web Service. As luck would have it, this is an extremely easy thing to do in SQL Server 2005 without requiring IIS.


CREATE PROCEDURE [dbo].[GetContacts]
AS
BEGIN
SET NOCOUNT ON;

SELECT TOP 20 [FirstName],[LastName] FROM
[Person].[Contact] ORDER BY [LastName];
END

Create HTTP Endpoint


Fire up SQL Server Management Studio, choose the AdventureWorks Database, and open a New Query Window. Create an HTTP Endpoint with the following statement:
CREATE ENDPOINT AW_Contacts
STATE = Started
AS HTTP
(
PATH = '/Contacts',
AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR),
SITE = '*'
)
FOR SOAP
(
WEBMETHOD 'GetContacts'
(NAME = 'AdventureWorks.dbo.GetContacts'),
WSDL = DEFAULT,
DATABASE = 'AdventureWorks',
NAMESPACE = DEFAULT
)
If you get an error executing this query that mentions another service using the port, make sure you turn off IIS.

A Path = '/Contacts', Site = '*', and Ports = (CLEAR) means that your URL will be to the default host, localhost, not require SSL, and look like the following:

http://localhost/contacts?WSDL

The FOR SOAP part of the command relates the GetContacts Method to the GetContacts Stored Procedure. Once you execute the command, take a peek at the Server Objects to see your HTTP Endpoint:


Fire Up Visual Studio 2005 to Consume the XML Web Service

At this point, you consume the XML Web Service like every other web service. Create a Windows Application, called Native Http, drop a DataGridView on the form, and add a Web Reference specifying the following URL:

http://localhost/contacts?WSDL



Leave the web reference name as localhost just so you can add the following code to the Form Load Event:

localhost.AW_Contacts contacts = new Native_Http.localhost.AW_Contacts();
contacts.Credentials = CredentialCache.DefaultCredentials;
dataGridView1.DataSource = (contacts.GetContacts()[0] as DataSet).Tables[0];

Run the application and magically see the contacts appear in the DataGridView.

No comments: