Create HTTP endpoint can be usefull when you want to retrieve data on database without to install iis or to develop an application, in this exemple we create a Web Service who getdata in XML format by using a web service.
STEP 1: Create the store procedure.
CREATE PROCEDURE
[dbo].[GetContacts] -->This store procedure retrieve Contacts from the
Contact table...
AS BEGIN SET NOCOUNT ON;
SELECT TOP 20 [FirstName],[LastName] FROM
[Person].[Contact] ORDER BY [LastName];
END
STEP 2: Reserve the HTTP name space.
When you create an HTTP endpoint, you need to check if you don't have an IIS server runing, or if the port 80 is in used, after that youmust reserve the name of yourwebsite by the following command:
sp_reserve_http_namespace N'http://MyServer:80' --> in this
exemple the site http://MyServer is
reserved.
GO
STEP 3: Create the HTTP Endpoint.
Select your database and execute this T-SQL script:
CREATE ENDPOINT AW_Contacts
STATE = Started
AS HTTP
(
PATH ='/Contacts',
AUTHENTICATION = (INTEGRATED), --> You can choose NTLM if you want...
PORTS = (CLEAR),
SITE ='http://myserver'
)
FOR SOAP
(
WEBMETHOD 'GetContacts'
(NAME ='AdventureWorks.dbo.GetContacts'),
WSDL = DEFAULT,
DATABASE ='AdventureWorks',
NAMESPACE = DEFAULT
)
Your HTTP Endpoint is created, to access type the following URL: http://myserver/Contacts?WSDL
NOTE: Don't forget to fix the "Connect" right for the accounts who access on this web service.