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.
To begin, you need to be running Windows 2003 or Windows XP SP2 to use this feature and you don’t need IIS.
My goal is pretty simple. I want to expose a stored procedure, called GetContacts, in my AdventureWorks Database as an XML Web Service without requiring IIS.
CREATE PROCEDURE [dbo].[GetContacts]
SET NOCOUNT ON;
SELECT TOP 20 [FirstName],[LastName] FROM
[Person].[Contact] ORDER BY [LastName];
Setp1. Create HTTP Endpoint
CREATE ENDPOINT AW_Contacts_Test
STATE = Started
PATH = ‘/MyContacts’,
AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR),
— CLEAR_PORT = 8045, –used to specify some port for the web service
SITE = ‘*’
(NAME = ‘AdventureWorks.dbo.GetContacts’),
WSDL = DEFAULT,
DATABASE = ‘AdventureWorks’,
NAMESPACE = DEFAULT
A Path = ‘/Mycontacts’, Site = ‘*’, and Ports = (CLEAR) means that your URL will be to the default host, localhost, not require SSL, and look like the following:
Setp2. Consuming the Web Service/Stored procedure in ASP.NET
Add a web reference in the web application using the URL: http://localhost/Mycontacts?WSDL and mention the Web Reference name as localhost.
Then write the code to access the web method wherever required as:
localhost.AW_Contacts_Test mySPAsWebService =
DataSet ds = new DataSet();
//To pass the default credentials as per the app pool
object result = mySPAsWebService.GetContacts();
foreach (object obj in result)
ds = (DataSet)obj;
//Populating the grid view with the results provided by the stored procedure
GridView1.DataSource = ds.Tables;