Exposing Stored Procedures As Web Services

SQL Server 2005 supports HTTP/SOAP endpoints that allow developers to expose database objects as web services.

Note 

Similar functionality existed in SQL Server 2000 with SQLXML 3.0. The major difference is that the earlier SQL Server version required the services of an external web server—IIS. That's not the case anymore. SQL Server 2005 uses the Windows Server 2003 kernel listener—HTTP.sys. However, you should not think of endpoints as a replacement for SQLXML; they have different and complementary features.

Endpoints

Only stored procedures or user-defined functions can be exposed through endpoints. They support two types of protocols—TCP and HTTP. They can also support different types of payload—database mirroring, Service Broker, T-SQL, and SOAP.

I will create an endpoint on a set of stored procedures for querying inventory in the Asset5 database:

 CREATE ENDPOINT ept_Asset!nventory        STATE = STARTED    AS HTTP    (        PATH = '/AssetInventory',        AUTHENTICATION = (INTEGRATED),        PORTS = (CLEAR),        SITE = 'Asset5'    )    FOR SOAP    (        WEBMETHOD 'AssetInventoryQuickByMakeModel'            (NAME='Assets.dbo.ap_InventoryByMakeModel_Quick3'),        WEBMETHOD 'AssetPhoto'         (NAME='Assets.dbo.ap_InventoryBy!d_GetPhoto),        WEBMETHOD 'AssetProperties'         (NAME='Assets.dbo.ap_InventoryProperties_Get'),        BATCHES = DISABLED,        WSDL = DEFAULT,        DATABASE = 'Asset5',        NAMESPACE = 'http://www.Asset5/AssetInventory'    ) 

The endpoint will listen to the HTTP or TCP port described in the As clause. The Site parameter is the name of your server and Path is the virtual folder that will be associated with the endpoint. The For clause defines a type of payload for the endpoint (for web services, put SOAP). It lists web methods and associated stored procedures and/or functions. The Batches parameter should be set to Disabled unless you want to allow users to send any type of query.

A client application can reference this endpoint on a URL that contains the Site and Path parameters (in this example, http://www.Asset5/AssetInventory). However, inside the .NET client application, the endpoint should be referenced using its name (in this example, ept_AssetInventory).