Native Web Services (also called HTTP Endpoints) is a new feature in SQL Server 2005. In fact, the concept of Endpoints itself is new, since you can also have other types of endpoints. Apart from the "native" aspect of web services, the concept of web services in SQL Server is however not new. If you have been using SQLXML in SQL Server 2000, then you are already familiar with this concept. SQLXML allowed HTTP based access to SQL Server and was installed as an ISAPI extension in IIS. Thus, you could send ad-hoc queries to the server or use a tool like Visual Studio to call specific stored procedures or functions in SQL Server. SQLXML also went one step further and allowed you to do "post processing" on the returned data. You can, for example, apply XSL stylesheets to render the output in a different format and so on.

The Native Web Services features builds upon what SQLXML originally provided with some important differences. If you were using SQLXML, you had to have an IIS service inbetween your application and SQL Server. When you submitted the query to IIS, there was a series of translation steps involved (which were done by SQLXML) and the request was finally sent over to SQL Server using TDS (tabular data stream). With SQL Server now being able to natively listen for HTTP request, all these intermediate steps go away and a client can now directly submit an HTTP request to SQL Server which is serviced. The following diagram illustrates this difference:

The way native web services work is quite simple. You create an HTTP Endpoint and expose stored procedures and functions through the endpoint. Application that can send and receive SOAP messages (like Visual Studio) can connect to the endpoint and request the data. The various objects that you expose through the endpoint are called services and since they are exposed over HTTP, you get the familiar term web services. All of these services are described using the standard WSDL (web services description language) format.

Note that native web services are supported only if SQL Server is running on Windows Server 2003 or Windows XP SP2. These versions of the operating system supports the new HTTP.SYS driver which is used by SQL Server for listening for HTTP requests.

In this article, we will see the syntax for creating endpoints and exposing services through them. We will also implement a Visual Studio 2005 smart client application that will access the services. Am not going to go into the details of creating the application and I assume that you can create them if I give you the screen shot and the appropriate code.

Creating the Endpoint

Creating the endpoint requires the usage of the new CREATE ENDPOINT T-SQL command. The syntax of the command has two parts. The first part describes information about the transport protocol (like HTTP, TCP etc) and the second part describes the payload (for example SOAP). In the example that we are going to create, we intend to expose 2 stored procedures and 1 function through the endpoint. Thus, before we see the syntax for the endpoint, let us define these objects. The following 3 scripts are to be run in the AdventureWorks database that ships with SQL Server 2005.

-- Get all the sales by territory
IF (OBJECT_ID ('dbo.getSalesByTerritory', 'P') IS NOT NULL)
	DROP PROCEDURE dbo.getSalesByTerritory
GO
CREATE PROCEDURE dbo.getSalesByTerritory AS
BEGIN
	SELECT
		t.Name,
		SUM(s.SalesYTD) AS [TotalSalesYTD],
		SUM(s.SalesLastYear) AS [TotalSalesLastYear]
	FROM
		Sales.SalesPerson s
		INNER JOIN Sales.SalesTerritory t ON s.TerritoryID = t.TerritoryID
	GROUP BY
		s.TerritoryID, t.Name

	RAISERROR ('Finished the execution of procedure.', 16, 1)
END
GO

The first script, we create a procedure called getSalesByTerritory that simply sums-up all the sales in each territory and returns the results back. There is also a RAISERROR statement that indicates the finish of the stored procedure. The presence of this statement will become clear when we write the client application.

-- Get the SQL Server information
IF (OBJECT_ID ('dbo.getServerInfo', 'P') IS NOT NULL)
	DROP PROCEDURE dbo.getServerInfo
GO
CREATE PROCEDURE dbo.getServerInfo AS
BEGIN
	DECLARE @serverInfo TABLE
		(
			[Index]			INT,
			[Name]			NVARCHAR(100),
			[Internal_Value]	NVARCHAR(200),
			[Character_Value]	NVARCHAR(200)
		)

	INSERT INTO @serverInfo EXEC master.dbo.xp_msver
	SELECT
		[Index], [Name],
		[Internal_Value], [Character_Value]
	FROM
		@serverInfo
END
GO

In the second script, we encompass a system defined stored procedure xp_msver as a stored procedure and return the results. The reason for this is that the result set returned by xp_msver is not directly exposed through the service (for reasons that I'm yet to find). Thus, this workaround is required, for now. The above script also shows a new T-SQL enhancement in SQL Server 2005. You can now populate a table variable with the execution of a stored procedure, which was not possible in SQL Server 2000. Here is the final script that we want.

-- Get the version of the SQL Server being exposed
IF (OBJECT_ID ('dbo.getServerVersion') IS NOT NULL)
	DROP FUNCTION dbo.getServerVersion
GO
CREATE FUNCTION dbo.getServerVersion() RETURNS NVARCHAR(MAX) AS
BEGIN
	RETURN (@@VERSION)
END
GO

The above function is very simple. It just wraps the @@VERSION global variable as a scalar function. This function also introduces the new NVARCHAR(MAX) datatype that is new in SQL Server 2005. The MAX datatypes have a size of 2GB and are intended to be a replacement for the traditional (N)TEXT and IMAGE datatypes.

Ok, now that we have all the methods to be exposed, let's see the endpoint creation itself. The following script creates the endpoint:

CREATE ENDPOINT AdventureWorksEP
	STATE = STARTED
AS HTTP
(
	PATH = '/sql',
	AUTHENTICATION = (INTEGRATED),
	PORTS = (CLEAR),
	SITE = '*'
)
FOR SOAP
(
	WEBMETHOD 'getSalesByTerritory'
		(NAME = 'AdventureWorks.dbo.getSalesByTerritory'),
	WEBMETHOD 'getServerInfo'
		(NAME = 'AdventureWorks.dbo.getServerInfo'),
	WEBMETHOD 'getServerVersion'
		(NAME = 'AdventureWorks.dbo.getServerVersion'),
	WSDL = DEFAULT,
	SCHEMA = STANDARD
)
GO

Let us now dissect the above command and understand each of its pieces (for the complete syntax, refer to the books online):

1. All endpoint have a name and in our example, the endpoint is named AdventureWorksEP
2. Endpoints have a state that indicates whether they are either started, stopped or disabled. In our example, we create the endpoint with a STARTED state
3. As indicated earlier, the first portion of the CREATE ENDPOINT statement is the protocol information. In our example, we are defining the protocol as HTTP
4. When you use the HTTP protocol, think of the definition as similar to what you would do in IIS. In IIS, you create a virtual directory, set its authentication and specify whether you need SSL or not and set other properties like IP restrictions etc. All of these operations have equivalent commands in the CREATE ENDPOINT command as indicated below:
   1. The PATH indicates the URL path for the endpoint
   2. The AUTHENTICATION indicates the type of authentication to use for the endpoint. Valid values are BASIC, DIGEST, NTLM, KERBEROS, INTEGRATED or a combination thereof. Anonymous access to an endpoint is not supported.
   3. The PORTS indicates the listening port types. In our example, we have specified CLEAR which will accept HTTP requests. The other value is SSL, which will only accept HTTPS requests
   4. The SITE indicates the name of the host computer that has to be used when using the URL. The * indicates all host names that are otherwise not reserved
5. Once the transport portion of the endpoint is done, we next define the payload that the transport will carry. In our case, we are using the SOAP payload. There are many options in the SOAP payload, but in our example we are using only 3 of them as described below:
   1. The WEBMETHOD specifies a method to which you can send HTTP requests. Each web method corresponds to exactly one method and you can specify multiple web methods similar to the example. When specifying the web method, you can include a namespace prefix
   2. Each web method is tied to a SQL Server object (stored procedure or function) indicated via the NAME option. If the object pointed to by NAME does not exist, a warning is given, but the DDL still succeeds. In our example, we create 3 web methods pointing to each of the objects that we created earlier
   3. The WSDL option indicates whether WSDL generation option is available for this endpoint. In our case, we have specified DEFAULT, which indicates that we want to use the built-in generator. You can also specify a custom stored procedure here or indicate that no WSDL generation has to happen by using NONE
   4. The SCHEMA option indicates whether inline schema information has to be generated. Specifying STANDARD attaches an inline schema with the responses. The inline schema is very important for clients like Visual Studio to generate the proxy classes and bind to DataSets

Querying endpoint Meta Data

Once you have created the endpoint, you can query it using a set of system views. The following are some of the views that we can use:

• sys.http_endpoints. This view contains a row for each endpoint configured for the HTTP protocol
• sys.endpoint_webmethods. This view contains a row for each method in an endpoint
• sys.endpoints. This view contains one row for every endpoint created
• sys.soap_endpoints. This view contains one row for each endpoint that has a SOAP payload

Let us use these meta data views to see how we can query information about our end point. Let us write a query that will give all the web methods implemented

SELECT
	e.name, e.protocol_desc, e.type_desc, e.state_desc,
	h.site, h.url_path,
	m.method_alias, m.object_name, m.result_format_desc
FROM
	sys.endpoints e
	INNER JOIN sys.http_endpoints h ON e.endpoint_id = h.endpoint_id
	INNER JOIN sys.endpoint_webmethods m ON e.endpoint_id = m.endpoint_id
WHERE
	e.name = 'AdventureWorksEP'

Consuming the Web Methods

Once we have defined the endpoint and it is in a started state, we can query the endpoint for the WSDL. You can also consume the web methods in a client environment like Visual Studio 2005 by using the traditional web references. You should however be aware of how the results are returned from the endpoint. For the SOAP payload, there is an option called FORMAT for each WEBMETHOD that allows you to specify what kind of results you want to send. Setting this value to ALL_RESULTS returns an object array that we then have to decode to understand what each element in the array is. If we set the option to ROWSETS_ONLY, then only the results of the query are returned. The default is to have ALL_RESULTS.

In this example, we will write a simple Windows forms application to consume the methods. Before getting into this, you can also use Internet Explorer to quickly check if the endpoint is working by typing: http://<server>/sql?WSDL. This should provide a page that contains the WSDL rendered from SQL Server.

The Windows interface that I built looks like the following:

The application has basically 3 buttons for testing the three methods, a grid for showing the results of any SELECT statements and a multi-line text box for showing other types of information. I leave it to you to design this form, but the following sections below contains the code that I have written in each of the handlers. Note also that you will need to add a web reference to the URL mentioned earlier and if everything was fine, Visual Studio would have found 1 service with the 3 methods in it. Call the reference adventureWorks (since I've used this name in my code).

Add the following code to the begining of the form class.

// Declare a reference to the web service
adventureWorks.AdventureWorksEP aw = new TestHTTPEndPoint.adventureWorks.AdventureWorksEP();

This declares a reference to the web service. The TestHTTPEndPoint is the name of my project and adventureWorks is the name for my web reference. Once you have done this, you can use aw to refer to all the methods in the web service.

Let us now write a reusable procedure that is used to display the results in the various controls.

private void displayResults (object[] results)
{
    string strResults = string.Empty;
    txtResults.Text = String.Empty;

    // Iterate through the array and decode each object
    foreach (object result in results)
    {
        switch (result.GetType().ToString())
        {
            case "System.Data.DataSet":
                // If its a dataset, we bind the first table to the grid
                DataSet ds = (DataSet) result;
                grdResults.DataSource = ds.Tables[0];
                grdResults.Refresh();
                break;

            case "TestHTTPEndPoint.adventureWorks.SqlRowCount":
                // If we get the rowcount, populate one of the labels
                adventureWorks.SqlRowCount rowCount = (adventureWorks.SqlRowCount)result;
                strResults = String.Format("Row(s) Returned {0}", rowCount.Count) + Environment.NewLine;
                break;

            case "TestHTTPEndPoint.adventureWorks.SqlMessage":
                adventureWorks.SqlMessage msg = (adventureWorks.SqlMessage)result;
                strResults = String.Format("Line: {0}, Procedure: {1}, Message: {2}", msg.LineNumber, msg.Procedure, msg.Message) + Environment.NewLine;
                break;

            case "System.Int32":
                // Indicates the procedure exit code which is populated in one of the labels
                int retCode = (int) result;
                strResults = String.Format("Return Value {0}", retCode) + Environment.NewLine;
                break;
        }
        txtResults.Text = txtResults.Text + strResults;
    }
}

This code does a few things. It takes in as parameter an object array and then iterates through the same. We check the type of each element to be one among the ones provided in the case block. If we find a match, we when conver the object to that type and then take the appropriate action. For example, when you execute a SELECT statement from a procedure, SQL Server displays a message about the number of rows affected. This gets translated as a SqlRowCount object in the client. Similarly, a RAISERROR in the code gets translated as a SqlMessage. Note that we get an object array, since we did not mention anything in the endpoint on the type of results that we need.

Once we have this function, it is easy to write the other methods. Here is the code for the [Sales By Territory] button.

private void cmdSalesByTerritory_Click(object sender, EventArgs e)
{
    // Call the web service. Since we have not specified the return type to have
    // only result sets, an array is returned
    try
    {
        object[] results = aw.getSalesByTerritory();
        displayResults(results);
    }
    catch (Exception error)
    {
        MessageBox.Show(error.Message, "Error!", MessageBoxButtons.OK);
    }
}

The code is very simple. We call the getSalesByTerritory method in the service and then pass on the return value to our reusable function. The code for the [Server Information] button is also similar.

private void cmdServerInfo_Click(object sender, EventArgs e)
{
    // Call the web service. Since we have not specified the return type to have
    // only result sets, an array is returned
    try
    {
        object[] results = aw.getServerInfo();
        displayResults(results);
    }
    catch (Exception error)
    {
        MessageBox.Show(error.Message, "Error!", MessageBoxButtons.OK);
    }
}

The final method to implement is the [Version] button. Here is the code for the same:

private void cmdVersion_Click(object sender, EventArgs e)
{
    DataSet ds = new DataSet();

    try
    {
        grdResults.DataSource = ds;
        grdResults.Refresh();

        txtResults.Text = (String) aw.getServerVersion();
    }
    catch (Exception error)
    {
        MessageBox.Show(error.Message, "Error!", MessageBoxButtons.OK);
    }
}

Once we have hooked up all the methods, you can then test out the program. The following is the output of a sample run.

Endpoint Security

Till now, we have not discussed about the user permissions that is required to create, alter endpoints. To manage and connect to endpoints, you will need the appropriate security. To define endpoints, the login in SQL Server must have CREATE ENDPOINT permission. For altering endpoints, the login must have the ALTER ANY ENDPOINT permission. When connecting the endpoint, the login connecting to SQL Server must have the CONNECT permission on the endpoint. All of these permissions can be grated using the GRANT option. For more information, refer to the SQL Server books online.

HTTP Endpoint Best Practices

The native web services capability of SQL Server 2005 is very useful. However, you should not get carried away and use it for all types of applications to gain HTTP access! Native web services are not well suited for high volume OLTP applications submitting lots of HTTP requests. For these things, SQLXML is still the better choice. Native web services are very useful when you have non-Microsoft application wanting to gain access to SQL Server in a seamless manner and also ad-hoc reporting environments that need data from a server. Note also that exposing SQL Server over the internet is not a good choice (even with tight security). Thus, the native HTTP access model is ideally suited for intranet types of applications.

Ok, that brings me to the end of this article of the new Native Web Service capabilities of SQL Server 2005. Do send me your feedback and comments to srisamp@gmail.com.