We 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.
SQL Server 2005 relies on 'HTTP.SYS' Kernel listener (driver) of Windows Server 2003 to expose its data objects as HTTP End points. It doesn't rely on IIS to expose these services. This simplifies application development and deployment, and requires fewer
infrastructure components.
Once the HTTP listener in Windows Server 2003 is configured in such a way that its able to send requests to SQL Server, The database administrator (DBA) can create and define the endpoints and associate the stored procedures and scalar-valued functions to an endpoint as Web methods.
Creating HTTP Endpoints
Setting up SQL Server as a Web Service that can listen natively for HTTP SOAP requests requires creating an HTTP endpoint and defining the methods that the endpoint exposes.
We 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.
The HTTP Endpoint also is a means of creating interfaces via HTTP or TCP for SOAP, TSQL, Service Broker, and even database mirroring. Although all these other functions are very fascinating, the discussion here mainly concerns only on the SQL Server 2005 ability to create Web services—Web services that can return row set data, scalar
values, messages, and even errors, all of which are serialized into XML automatically.
While creating an HTTP endpoint, it must be created with a unique URL that it uses this URL to listen for incoming HTTP requests. For example, if we create an endpoint with the URL "http://ServerName.Domain/Store" SOAP requests that are sent to http://ServerName.Domain/Store" will be picked up by HTTP.SYS. HTTP.sys will then route the SOAP requests to the SQL Server instance that hosts the endpoint associated with the URL. From there, the requests will be handed off to the SOAP processing layer within SQL Server.
Snippet:
In this section how we can create the HTTP endpoint. To start with, we will have to create a stored procedure that returns some data or use an existing stored procedure. As an example, we will use a simple stored procedure dbo.getCustomerDetails that returns the contents of two fields from the Sales.Customers table in the Sales sample database
that ship with SQL Server 2005:
....................................................
CREATE PROC dbo.getCustomerDetails
AS
SELECT
CustomerID,
Name
FROM
Sales.Customers
....................................................
Once the stored procedure is ready, the next step will be to create
the HTTP endpoint. The SQL Statement snippet below is used to create
an HTTP endpoint "GetCutomer" and running this SQL statement is what
makes the data from the stored procedure available to SOAP clients:
....................................................
CREATE ENDPOINT GetCustomer
STATE = STARTED
AS HTTP
(
PATH = '/Store',
AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR),
SITE = 'ServerName.Domain' --SQL Server Name and Domain
)
FOR SOAP
(
WEBMETHOD 'CustomerList'
(NAME= Sales.dbo.getCustomerDetails),
BATCHES = DISABLED,
WSDL = DEFAULT,
DATABASE = 'Sales',
NAMESPACE = 'http://Sales/Store'
)
GO
....................................................
The CREATE ENDPOINT statement has several key components. The STATE argument is set to STARTED, which indicates that the endpoint listener is running. Other options included STOPPED and DISABLED for handling service endpoints. The rest of the code sits in the AS HTTP clause or the FOR SOAP clause. AS HTTP sets HTTP as the transport for the endpoint and includes the following settings:
PATH—Specifies the URL for the service endpoint, and in this example defaults to the server name and the path specified, e.g. "http://ServerName.Domain/Store
AUTHENTICATION—Specifies the type of authentication to be used for the service endpoint. A variety of authentication mechanisms are supported, including BASIC, INTEGRATED (uses Windows security), NTLM, and KERBEROS.
PORTS—Specifies what port type to use. CLEAR or SSL are the supported options, with CLEAR accepting only HTTP requests and SSL requiring HTTPS.
SITE—Specifies the name of the host computer for the endpoint. The FOR SOAP clause supports the following arguments:
WEBMETHOD—Specifies the Web method used to send requests via the HTTP SOAP endpoint. we can declare multiple Web methods per endpoint.
BATCHES—specify whether not the endpoint supports ad-hoc SQL requests.This is disabled by default.
WSDL—specifies whether the endpoint supports WSDL. For custom WSDL implementations, we can provide a stored procedure name that returns a custom WSDL implementation.
DATABASE—specifies the name of the database where the requested operation is executed in context.
NAMESPACE—specifies a namespace for the endpoint.After creating the endpoint, we can submit an HTTP request to the server to ensure that the endpoint is responding. A simple test is to navigate to "http://ServerName.Domain/Store?wsdl (substitute server
and endpoint name), which, if the endpoint is responding, will return the WSDL for our exposed service.
Consuming Web Services(End Point)
Consuming the Native Web Service that is created using the SQL Server 2005, Is exactly same as we consume any web services created using .Net and C#. That's the advantage of Web Services and SOAP. We'll create a windows or a web application in Visual studio .Net and we will need to add the Web reference to the web service created using the HTTP Endpoint. We can browse the web service created in the above sample code using the http://servername.domain/Store?wsdl where the Server would be our computer name or IP.
Once the Web reference is created we can use this web service as a normal web service. Here we need to note that the WebMethod returns the object array because the SQL Server can not only send the result sets, but also the messages, Errors, and other types. Its our responsibility that we validate what we have received is what we expected and safely cast the object to the required type to work with it.
......................................................................................................................
SQLReference.GetCutomer proxy = new SQLReference.GetCutomer ();
//SQLReference is the name given to the Web Service Reference
proxy.UseDefaultCredentials = true;
object results = proxy.CutomerList();
...............................................................................................
Comments
Post a Comment