Xml Web Services in Sql Server 2005

Database products are making it easier and easier to hook up Internet protocols directly to your data. Microsoft SQL Server is a case in point. While Microsoft SQL Server 2000 did allow Web data access, the process required to use the SQLXML library and a full installation of Internet Information Services. With SQL Server 2005, Microsoft has solved this issue.Now, you can have direct access to your SQL Server data from any HTTP SOAP client without any extra middleware at all - not even IIS.

Description:

Microsoft continues to bet heavily on Web Services as a backbone for service-oriented architectures, so it makes sense that they've chosen to expose SQL Server 2005 data via a Web Service. Or, to be more precise, you can create as many Native XML Web Services as you like in SQL Server 2005.

To do so, you use the new CREATE ENDPOINT statement to create HTTP endpoints. Each HTTP endpoint ties the results of a SQL Server stored procedure directly to a Web Service, providing support through the Web Services Description Language (WSDL) and Simple Object Access Protocol (SOAP) protocols for retrieving the data supplied by the stored procedure. SQL Server interfaces directly with the Windows HTTP listener process (http.sys) so that SOAP requests are routed directly to SQL Server, with no intervening middleware necessary. Similarly, SOAP responses are sent directly back to the requesting client.

Steps:
1)Create a simple Stored Procedure

CREATE PROC dbo.SalesStoreProc AS
SELECT ProductID, ProductName FROM Sales.Product

2)Creating an HTTP Endpoint

The next step is to create the HTTP endpoint. Running this SQL statement is what makes the data from the stored procedure available to SOAP clients:

CREATE ENDPOINT GetStores STATE = STARTED
AS HTTP( PATH = '/Store', AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR),
SITE = 'localhost')
FOR SOAP
( WEBMETHOD 'StoreList'
(NAME='AdventureWorks.dbo.SalesStoreProc'),
BATCHES = DISABLED,
WSDL = DEFAULT,
DATABASE = 'AdventureWorks',
NAMESPACE = 'http://AdventureWorks/Store')
GO

Key things to be noted:

• The STATE clause specifies the initial state of the endpoint. It can be started, stopped (listening but returning errors to clients) or disabled (not even listening for requests)
• The AS HTTP clause specifies the transport protocol to use. You can also specify AS TCP here.
• The PATH clause specifies the URL on the server that clients will use to reach this Web service.
• The AUTHENTICATION clause specifies how clients will authenticate themselves to the SQL Server: BASIC, DIGEST, NTLM, KERBEROS, or INTEGRATED.
• The PORTS clause specifies whether the service will listen on the CLEAR or SSL ports, or both (other clauses, not shown here, let you specify non-standard port numbers)
• The SITE clause lets you specify a hostname for the computer that will respond to requests.
• The FOR SOAP clause states that this endpoint will respond to SOAP messages. Other endpoints handle messages for Service Broker or database mirroring.
• The WEBMETHOD clause defines a Web method, mapping a method name to the name of a stored procedure
• The BATCHES clause specifies that this endpoint won't process arbitrary SQL statements.
• The WSDL clause specifies that it will provide WSDL support.
• The DATABASE clause specifies the database that contains the data.
• The NAMESPACE clause specifies the XML namespace for the messages.

Testing the Webservice:

The URL for the WSDL file (http://localhost/Store?wsdl) is determined by the CREATE ENDPOINT statement. The Web server is the server where SQLServer 2005 is installed. The PATH clause dictates the Store portion of the URL. The remainder of the URL is where SQL Server listens, by convention, for WSDL requests associated with this particular Web Service.
Thus SQL Server will accept, and respond to, standard HTTP SOAP 1.2 requests. This gives you a supremely easy way to hook SQL Server 2005 data into any sort of service-oriented architecture that you can dream up.