Native XML Web Services for Microsoft SQL Server 2005

Intro :
Microsoft SQL Server 2005 provides a standard mechanism for accessing the database engine using SOAP via HTTP. Using this mechanism, you can send SOAP/HTTP requests to SQL Server to execute:

• Transact-SQL batch statements, with or without parameters.
• Stored procedures, extended stored procedures, and scalar-valued user-defined functions.

Requirements

SQL Server 2005-native Web services require Microsoft Windows Server 2003 as the operating system, because they rely on the kernel mode http driver http.sys that this version provides. Since SQL Server leverages the kernel mode http.sys driver, you do not necessarily need to have IIS installed to expose Web services out of SQL Server; this simplifies administration. Instead, you should base your decision to install IIS on application requirements. For example, certain applications benefit from having an explicit middle tier. In such cases, IIS would be useful.

CREATE HTTP ENDPOINT

HTTP Endpoints are created and administered using Transact-SQL DDL. Creating an HTTP Endpoint is the first step in enabling HTTP/SOAP access to SQL Server 2005. Each endpoint has a name and a collection of options that when combined define the behavior of the endpoint.

To illustrate how the CREATE HTTP ENDPOINT is used, let's take a look at a Hello World example for invoking a stored procedure via SQL Server Web Services.
First, create a stored procedure called hello world in the master database, using the following T-SQL. This stored procedure simply displays the string provided in the input parameter.

CREATE PROCEDURE hello_world(@msg nvarchar(256))AS

BEGIN
select @msg as 'message'
END

Next, use the following T-SQL to create the HTTP endpoint which will allow access to this stored procedure as a WebMethod:

CREATE ENDPOINT hello_world_endpoint
STATE = STARTEDAS HTTP ( AUTHENTICATION = ( INTEGRATED ),
PATH = '/sql/demo', PORTS = ( CLEAR ))
FOR SOAP ( WEBMETHOD 'http://tempuri.org/'.'hello_world'
(NAME = 'master.dbo.hello_world'),
BATCHES = ENABLED,
WSDL = DEFAULT )

All endpoints are stored in master, in the metadata view master.sys.http_endpoints. An endpoint doesn't have any SOAP Methods unless you define them. In the above example, we exposed the stored procedure master.dbo.hello_world as WebMethod 'hello_world'; the WebMethod can have any name and, for example, could have been called as 'testproc1' under the 'http://tempuri.org' namespace. Specifying DEFAULT as the value for WSDL clause enables the endpoint to respond to requests for WSDL generating WSDL using the default format. You can suppress WSDL generation by setting WSDL=NONE in the above statement. We discuss the details of WSDL generation in a subsequent section

WSDLWSDL is a document written in XML that describes a Web service. It specifies the location of the service and the operations (or methods) the service exposes. WSDL provides the information necessary for a client to interact with a Web service. Tools such as Visual Studio .NET and Jbuilder use the WSDL to generate proxy code that client applications can use to communicate with a Web service. If the endpoint has WSDL enabled, that endpoint will produce WSDL when it receives a request for it. The endpoint created earlier in this article will produce WSDL when an authenticated request is sent to it. A WSDL request is a simple HTTP get request of the form.

http://servername/sql/demo?wsdl

For more details Turn to : http://msdn.microsoft.com/en-us/library/ms345123.aspx