Page 1 of 1

How to Configure HTTP Endpoints in SQL Server

Posted: Mon Jun 24, 2019 1:56 pm
by sqltips
SQL Server 2005 provides native Hypertext Transfer Protocol(HTTP) support that allow us to create Web Service on the database server. These web service exposed Web Methods that the web application can access by endpoints, so that client can directly access these services over the internet.

This endpoints is the gateway through which http-based clients can send queries to the server, an http endpoints listen and receives client request on port 80.After establishing an http endpoints, you can create store procedures or user defined functions that can be made available to endpoints users.

The SQL Server instance provides a WSDL generator that helps generate the description of a web service in the WSDL format, which is used by the clients to send requests.

To Secure your data you can secure your endpoints by granting permissions to only selected users to access an HTTP endpoint.

How:

Creation of required Database code
Creation of HTTP End Point Object
Finally verify the creation
Example(Sample Code)/Syntax:

Create ENDPOINT hr_WeeksNumber STATE = STARTED AS HTTP( /* PATH = 'url' where the endpoints will be stored on the host computer */ PATH='/HR', AUTHENTICATIoN =(INTEGRATED), Ports = (CLEAR), SITE = 'localhost') For SOAP ( WEBMETHOD 'pro_ListofWeeks' ( name = 'testdb.dbo.prolistofWeeks', FORMAT =ROWSETS_ONLY), /* RowSet = returns only the result sets. */ WSDL= DEFAULT, SCHEMA = STANDARD, DATABASE = 'TESTDB', NAMESPACE =' http://tempUri.org/ ');

-- Create Database object(Procedure) Create procedure [dbo].[pro_ListofWeeks] AS Declare @CWeek int select @CWeek =Datepart(wk,getdate()) --Print @CWeek set @CWeek=@CWeek-2 exec pro_Week @CWeek

-- Procedure Week--

CREATE procedure pro_Week @week integer as set nocount on create table #temp ( id integer) while (@week>=1)
begin insert into #temp
values (@week)
set @week=@week-1
end set nocount off
select ID as WeeksAvilable, 'Week: ' + Convert(varchar,id) as WeekNo from #temp order by 1 desc

Conclusion

To Verify the creation of endpoints you just need to create one client application, for an example if you create the client application using c#, you need to add web reference first After that you can access the newly created application.