by Peter DeBetta of Winellect

SQL Server 2000 offers some capabilities for returning XML output via HTTP using SQLXML—and, of course, SQLXML supports Web services creation. Although not rocket science, setting up, configuring, and using Web services in SQL Server 2000 does require a little effort (see the http://www.microsoft.com/sql/techinfo/xml/default.asp SQLXML documentation about Web services in SQL Server 2000).

When .NET came about, people began writing Web services in C# or VB.NET. They simply connected into SQL Server (or any data store, for that matter) to retrieve the data. .NET made creating Web services as simple as 1-2-3:

1. Create some stored procedures.
2. Write some WebMethods to return the data from said stored procedures.
3. Presto! You have a Web service.

But now, the next generation of Web services is hitting the streets. SQL Server 2005 supports native HTTP SOAP via a feature known as HTTP Endpoints. For those who don't know, Simple Object Access Protocol (affectionately known as SOAP) is a lightweight messaging protocol that Webopedia defines as follows:

"Short for Simple Object Access Protocol, a lightweight XML-based messaging protocol used to encode the information in Web service request and response messages before sending them over a network. SOAP messages are independent of any operating system or protocol and may be transported using a variety of Internet protocols, including SMTP, MIME, and HTTP."

This new HTTP Endpoints feature in SQL Server 2005 is the subject of this article.

HTTP Endpoints

So what is an HTTP Endpoint? You may have heard that it is SQL Server 2005's means for creating Web services, but it actually is much more. An HTTP Endpoint also is a means of creating interfaces via HTTP or TCP for SOAP, T?SQL, Service Broker, and even database mirroring. Although these other functions are very intriguing, this discussion concerns only the ability to create Web services in SQL Server—Web services that can return rowset data, scalar values, messages, and even errors, all of which are serialized into XML automatically. And, an HTTP Endpoint does all of this without requiring you to install IIS (it uses the Windows 2003 kernel module http.sys).

Because I like to learn from examples, I am going to teach by example. I demonstrate how to use HTTP Endpoints by walking through an example from start to finish. Before starting, here are a few notes to consider:

• This demonstration uses the new demo database called AdventureWorks.
• Native HTTP SOAP in SQL Server 2005 is not supported on Windows XP. If you want to try the examples, you need Windows Server 2003.
• SQL Server 2005 Express Edition (the new MSDE) does not support HTTP Endpoints, so be sure to install the Developer Edition.
• I do not explain how to use SQL Server Management Studio to execute SQL scripts. I trust you know how to do this.

Security

Because HTTP Endpoints are a server-level feature, security of endpoints is also on the server level. Of course, the serveradmin system role can create, alter, and drop endpoints from the SQL Server instance, but how do you allow developers to manage these endpoints without giving them the excessive permissions of this system role?

The answer lies in SQL Server 2005's new ability to assign server-level permissions to logins, as shown here:

GRANT ALTER ANY HTTP ENDPOINT TO peter

With this command, you can allow the peter login to manage HTTP Endpoints without giving other unnecessary permissions. The following is a list of permissions that you can modify via GRANT, DENY, or REVOKE:

• {GRANT|DENY|REVOKE} ALTER ANY ENDPOINT TO Login

  Controls the ability to alter any HTTP Endpoint; also allows permission to transfer ownership and connect to any endpoint

• {GRANT|DENY|REVOKE} ALTER ON ENDPOINT:: EndPointName TO Login

  Controls whether a login can alter a specific HTTP Endpoint; also allows permission to transfer ownership and connect to the specified endpoint

• {GRANT|DENY|REVOKE} CONTROL ON ENDPOINT:: EndPointName TO Login

  Controls whether a login can alter or drop a specific HTTP Endpoint; also allows permission to transfer ownership and connect to the specified endpoint

• {GRANT|DENY|REVOKE} CONNECT ON ENDPOINT:: EndPointName TO Login

  Controls whether or not a login can connect to (execute requests against) an HTTP Endpoint

• {GRANT|DENY|REVOKE} TAKE OWNERSHIP ON ENDPOINT:: EndPointName
  TO Login
  

  Controls whether a login can take ownership of the HTTP Endpoint

• {GRANT|DENY|REVOKE} VIEW DEFINITION ON ENDPOINT:: EndPointName
  TO Login
  

  Controls the ability for a login to see the metadata (definition) of the HTTP Endpoint

Consider one last example. If I want to deny the peter login the ability to use an HTTP Endpoint named SQLEP_Financial, I would execute the following statement:

DENY CONNECT ON ENDPOINT::SQLEP_Financial TO peter

Providing a Source for Data

The first order of business is to create the code components that will be the source of information for the endpoint. I create two stored procedures and one user-defined function (UDF), as shown in Listing 1:

Listing 1

USE AdventureWorks
GO
CREATE PROCEDURE prProductList
AS
SELECT   Product.ProductID, Product.ProductNumber, Product.Name
FROM     Production.Product AS Product
         INNER JOIN Production.ProductInventory AS Inventory
             ON Product.ProductID = Inventory.ProductID
         INNER JOIN Production.Location AS Location
             ON Inventory.LocationID = Location.LocationID
WHERE    Product.ListPrice > 0
 AND     Location.LocationID = 6 --Products from Misc Storage only
ORDER BY Product.Name
GO
CREATE PROCEDURE prProductStockInfo
    @ProductID int
AS
IF (SELECT ListPrice FROM Production.Product 
    WHERE ProductID = @ProductID) = 0 
 BEGIN
    RAISERROR ('Product not available for retail sale', 11, 1)
 END
ELSE
 BEGIN
    SELECT  Product.ProductID, Product.ProductNumber, 
            Product.Name, Product.Weight, 
            Product.WeightUnitMeasureCode,
            Product.ListPrice, Inventory.Quantity
    FROM    Production.Product AS Product
        INNER JOIN Production.ProductInventory AS Inventory
            ON Product.ProductID = Inventory.ProductID
        INNER JOIN Production.Location AS Location
            ON Inventory.LocationID = Location.LocationID
    WHERE   Product.ProductID = @ProductID
     AND    Location.LocationID = 6 --Misc Storage
 END
GO
CREATE FUNCTION dbo.fnProductPhoto (@ProductID int)
RETURNS varbinary(MAX)
AS
 BEGIN
    DECLARE @largePhoto varbinary(max)

    SELECT  @largePhoto = ProdPhoto.LargePhoto 
    FROM    Production.ProductPhoto AS ProdPhoto
    INNER JOIN Production.ProductProductPhoto ProdProdPhoto
        ON ProdPhoto.ProductPhotoID = ProdProdPhoto.ProductPhotoID
    WHERE   ProdProdPhoto.ProductID = @ProductID

    RETURN (@largePhoto)
 END

The first procedure returns a list of products (product ID, name, and number) from the AdventureWorks database. The second procedure returns more details about a product, including inventory levels. The UDF returns a single varbinary value containing the product photo.

Creating the Endpoint

Once you have your procedures and functions set up, you can create the endpoint (Web service). The following code creates the endpoint on my server:

CREATE ENDPOINT SQLEP_AWProducts
    STATE = STARTED
AS HTTP
(
    PATH = '/AWproducts',
    AUTHENTICATION = (INTEGRATED),
    PORTS = (CLEAR),
    SITE = 'win2k301'
)
FOR SOAP
(
    WEBMETHOD 'ProductList'
        (NAME='AdventureWorks.dbo.prProductList'),
    WEBMETHOD 'ProductStockInfo'
        (NAME='AdventureWorks.dbo.prProductStockInfo'),
    WEBMETHOD 'ProductPhoto'
        (NAME='AdventureWorks.dbo.fnProductPhoto'),
    BATCHES = DISABLED,
    WSDL = DEFAULT,
    DATABASE = 'AdventureWorks',
    NAMESPACE = 'http://Adventure-Works/Products'
)

Believe it or not, you are now ready to consume this Web service. Before doing that, though, examine the CREATE ENDPOINT statement. Starting from the top, the first thing you will notice is the name of the endpoint, SQLEP_AWProducts, which is how you refer to the Web service within your client code (see Figure 1). PATH is the virtual path on the server for the Web service. So, for this example, you would access the Web service at http://win2k301/AWproducts, but it would be called SQLEP_AWProducts in your .NET application code. Table 1 provides explanations for this and other arguments used in the AS HTTP clause.

[Image1.gif]

Figure 1: Object Browsing the Web Service in Visual Studio.NET 2005

Table 1: Arguments Used in the AS HTTP Clause

The STATE argument can have one of three values:

• STARTED—listening and responding
• DISABLED—neither listening nor responding
• STOPPED—listening, but returns errors to client requests

You can change the state of an existing Web service using the ALTER ENDPOINT statement. For example, to disable the endpoint, you can execute:

ALTER ENDPOINT SQLEP_AWProducts STATE = DISABLED

The FOR SOAP clause determines which methods the Web service will expose, which other features are exposed (dynamic SQL, for example), and other characteristics of the Web service. In this example, three methods are exposed: the two stored procedures and the UDF shown in Listing 1. WEBMETHOD requires the name of the exposed method as its first argument. As you can see in the code, it does not have to match the name of the actual stored procedure or UDF that it is exposing. The next argument of WEBMETHOD is the fully qualified NAME of the object you are exposing.

The BATCHES argument specifies whether or not the Web service supports ad hoc queries. WSDL indicates how the WSDL document generation will occur; DEFAULT indicates that the WSDL document will be created automatically. SQL Server 2005 allows you to implement custom WSDL generators using the CLR-based abilities of SQL Server 2005 (see Books Online for more details). DATABASE is the name of the database that will be accessed from the endpoint (endpoints are defined at the server level). And finally, NAMESPACE is, well, the namespace for the endpoint.

You have now officially created a Web service using SQL Server 2005. Congratulations.

The Client to Consume Your Web Service

Consuming a Web service created in SQL Server 2005 as an HTTP Endpoint is similar to consuming a Web service created in C#. That's the beauty of Web services and SOAP. Begin a new Windows application in Visual Studio.NET and add the following controls to the form (ControlType: name):

• Button: btnExecSP
• DataGridView: dgvProduct
• ListBox: lstProducts
• PictureBox: picProduct

You will need to add the Web reference before writing any code. Again, you can browse to http://server/awproducts?wsdl to create the Web reference. Keep in mind that your Web reference object will be named server_AWProducts (my machine is named win2k301 so I browse to http://win2k301/awproducts?wsdl, and my Web reference object is win2k301_AWProducts). You will have to tweak the code to the Web reference name that you create.

Once the Web reference is in place, you can double-click the Button control and add the code in Listing 2.

Listing 2

private void BtnExecSP_Click(System.Object sender, System.EventArgs e)
{
   win2k301_AWProducts.SQLEP_AWProducts proxy = 
      new win2k301_AWProducts.SQLEP_AWProducts();
   proxy.Credentials = System.Net.CredentialCache.DefaultCredentials;

   object[] products = proxy.ProductList();
   System.Data.DataSet resultDS;

   if (products[0].ToString() == "System.Data.DataSet")
   {
      resultDS                  = (System.Data.DataSet)products[0];
      DataTable dt              = resultDS.Tables[0];
      lstProducts.DataSource    = dt;
      lstProducts.DisplayMember = "Name";
      lstProducts.ValueMember   = "ProductID";
   }
}

The first thing you will notice is that the Web method returns an object array. Because SQL Server can actually send back not only result sets, but also messages, errors, and other types (see Table 2 for more details), you should validate that you received what you expected. Listing 2 does a check by using the ToString method on the first element in the returned object array and comparing it to "System.Data.DataSet". If true, you know you can safely cast the object to a DataSet type and work with it from there.

Table 2: Object Return Types

Now double-click the ListBox control and add the following code:

private void lstProducts_SelectedIndexChanged(object sender,
                                              System.EventArgs e)
{
   Int32 i = 0;
   try 
   {i = System.Convert.ToInt32(lstProducts.SelectedValue.ToString());}
   catch 
   {return;} //prevents issue when loading listbox

   object e1;
   win2k301_AWProducts.SqlMessage errorMessage;
   System.Data.DataSet resultDS;

   win2k301_AWProducts.SQLEP_AWProducts proxy = 
      new win2k301_AWProducts.SQLEP_AWProducts();
   proxy.Credentials = System.Net.CredentialCache.DefaultCredentials;

   object[] products = proxy.ProductStockInfo(i);
   e1 = products[0];
   switch (e1.ToString())
   {
      case "System.Data.DataSet":
         resultDS = (System.Data.DataSet)products[0];
         DataTable dt = resultDS.Tables[0];
         dgvProduct.DataSource = dt;
         dgvProduct.Refresh();
         break;
      case "Yukon_Http_v1.win2k301_AWProducts.SqlMessage":
         errorMessage = (win2k301_AWProducts.SqlMessage)products[0];
         MessageBox.Show("Error fetching product", "'" 
            + errorMessage.Message + " occurred at '" 
            + errorMessage.Source + "'");
         return;
   }
   Byte[] photo = proxy.ProductPhoto(i);
   picProduct.Image = new Bitmap(new MemoryStream(photo));
}

You should pay attention to a couple of details here. First, this code snippet checks for another type of returned object: SqlMessage. Notice the type name (from the ToString method) is Yukon_Http_v1.server_virtualDirectory.SqlMessage. This type is defined in the WSDL document and is unique to each Web service. The following listing shows a snippet from the WSDL of the SqlMessage complexType:

<xsd:complexType name="SqlMessage">
  <xsd:sequence minOccurs="1" maxOccurs="1">
    <xsd:element name="Class" type="sqlmessage:nonNegativeInteger" />
    <xsd:element name="LineNumber"
                 type="sqlmessage:nonNegativeInteger" />
    <xsd:element name="Message" type="xsd:string" />
    <xsd:element name="Number" type="sqlmessage:nonNegativeInteger" />
    <xsd:element name="Procedure" type="xsd:string" minOccurs="0" />
    <xsd:element name="Server" type="xsd:string" minOccurs="0" />
    <xsd:element name="Source" type="xsd:string" />
    <xsd:element name="State" type="sqlmessage:nonNegativeInteger" />
  </xsd:sequence>
</xsd:complexType>

The last tidbit for this discussion is the Byte array that is returned from the UDF. Because this UDF returned varbinary data, its XSD type is xsd:base64Binary. Thus, it is a Byte array in .NET. SQL Server's int data type is the XSD type xsd:int and Int32 in .NET. Books Online has more information about the mapping of types.

Easy Web Service Creation

HTTP Endpoints provide a way to create various interfaces into SQL Server, including features such as Service Broker, database mirroring, and of course, Web services. It makes the creation of a Web service (that exposes SQL Server data) a snap for experienced programmers and database administrators alike. Enjoy!

About the Author

Peter DeBetta is an independent software consultant who trains exclusively for Wintellect. Peter regularly publishes on the topic of SQL Server and recently wrote Introducing SQL Server 2005 for Developers for Microsoft Press. He also speaks at conferences such as VSLive!, WinSummit, WinDev, and Devscovery.