How to create HTTP Endpoint in SQL Server 2005

Publish date: Wednesday June 04, 2008
Malgorzata Szabelska

Native Web Services were first introduced in SQL Server 2005. This database server can act like an ASP.NET Web Service and can respond to HTTP Requests. It is accessible through SOAP protocol and XML. Like typical Web Services, SQL Server 2005 accepts SOAP request and sends response in XML. However, there is no need to use IIS Server because SQL Server 2005 can take requests directly through the Internet. This works by means of http.sys driver which is a new functionality of Windows 2003 Server family products and is a part of a network system.

In a database language Web Service is called endpoint. To create an endpoint you need to have some procedures or functions in the database that you want to expose. For example, a procedure can look like this:

You can also expose a function:

To create your endpoint you can use similar syntax:

This tutorial command creates SQL Server 2005 endpoint named Netrostar. Its state is set to STARTED so it will be ready to use immediately after execution. Other possibilities for this argument are: STOPPED and DISABLED. When state is set to STOPPED we will receive an error message from the endpoint. However, if it is set to DISABLED we don't even know whether endpoint really exists. Next, in the 'AS HTTP' block, we can specify some protocol arguments, for example:

  • Path to endpoint
  • Authentication type (BASIC, DIGEST, INTEGRATED)
  • Connection type (CLEAR, SSL)
  • Site name (use 'localhost' for tests)

The last thing is to define which methods are accessible to clients. This declaration is done in the 'FOR SOAP' block. Every method is preceded by the WEBMETHOD word. Then you put the name you want each method to be called but its real name in database must consist of 3 parts: database name, schema owner and method name. You can choose if you want to send XSD schema with the response and specify its format (ALL_RESULT gets in addition number of records and some warnings or errors if they exist). The WSDL argument specifies if you want to show endpoint description in XML. The last argument in this sample is very significant. With it we can enable or disable executing client queries. For security purposes it is good to disable this functionality. We can imagine what could happen when somebody has gained access to some secret information or tried to execute 'DROP DATABASE' command.

How to use SQL Server 2005 HTTP endpoint

Using HTTP endpoint is very simple. You can create Windows .NET Application in Visual Studio 2005. The first thing is to add web reference. You can do it by right-clicking on the solution explorer. Then you have to write path to your endpoint's WSDL document. It is the address to your web service connected with the '?WSDL' ending (for example When you find this endpoint you have to add web reference name (for example 'Endpoint') and click 'add reference' button. After you have done this you can simply execute its methods:


Author: Edie Uyesugi

Latest Tutorials Click here to see All Tutorials
How to save and get images from database How to save and get images from database
How to create HTTP Endpoint in SQL Server 2005 How to create HTTP Endpoint in SQL Server 2005
In-store video advertisement In-store video advertisement Online payment processing gateway Online payment processing gateway Tracking & rating with .NET shipping components Tracking & rating with .NET shipping components
Copyright Netrosoft @ 2007 - 2018