This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

Easily Creating Web Services and Their Clients

Rick Dobson

Although the SQL Server 2000 Web Services Toolkit has been around for almost two years, a lot of organizations ignored it initially. Now that both Web Services and .NET are more widely accepted, it may be time for you to explore (and perhaps even champion!) the technology. Rick Dobson explains.

Web Services have been a hot topic for quite a while now, but the SQL Server 2000 Web Services Toolkit remains a little-known way to easily create Web Services. Although the toolkit makes it a snap to whip up a Web Service based on a SQL Server stored procedure or user-defined function (UDF) without any code at all, it does take a few lines of code to create a client application for a Web Service. Furthermore, one of the easiest ways to build the client is with the .NET Framework. As organizations deploy the .NET Framework to more computers, interest in the SQL Server 2000 Web Services Toolkit may grow. The exposing of SQL Server objects via Web Services is particularly important because it leverages existing SQL Server databases in more contexts. Basically, Web Services create more ways for your SQL Server databases to serve an organization. This article illustrates how to build the client for a Web Service based on a stored procedure in a SQL Server database, and how to create a Windows form client with VB.NET.

Quick review of Web Services

Web Services enable remote procedure calls on one computer from a second computer. A Web Service can expose SQL Server objects as Web methods, which operate much like "normal" methods in an object model. When one computer invokes a Web method on another computer, the first computer remotely invokes the procedure (or Web method). Web Services use SOAP (Simple Object Access Protocol) as a format for passing content back and forth between a pair of computers. While you can use Web Services between two applications on the same computer, the much more interesting case is to interface via SOAP with another computer running a non-Windows operating system to a computer running SQL Server.

The SQL Server 2000 Web Services Toolkit implements a Web Service on a middle tier between a Web Service client application and a SQL Server database. The Web Service components exist in a virtual directory for Microsoft Internet Information Services (IIS), the Microsoft Web server. The SQL Server 2000 Web Services Toolkit provides a Microsoft Management Console for creating and configuring Web Services.

Using the Web Services Toolkit

In order to run the SQL Server 2000 Web Services Toolkit, you need access to both SQL Server 2000 and IIS 4.0 or higher. (The toolkit itself runs on Windows 2000, Windows NT, and Windows XP operating systems.) In addition to the Web Services Toolkit, you'll also need the SOAP 2.0 Toolkit on the server from which you run the Web Services Toolkit.

One reason that more SQL Server professionals may not have experimented with the Web Services Toolkit is that the toolkit is available as a separate download—it doesn't ship in the box, since it was released after the initial ship date for SQL Server 2000. You can download the Web Services Toolkit as part of SQL Server 2000 Web Release 3 (SQLXML 3.0) from www.microsoft.com/downloads/details.aspx?FamilyId=4023DEEA-F179-45DE-B41D-84E4FF655A3B&displaylang=en. You should also download the SP2 version of the SOAP 2.0 Toolkit from www.microsoft.com/downloads/details.aspx?FamilyId=147ED727-0BE8-48A1-B1DA-D50B1EA582CB&displaylang=en.

After you download and install the two toolkits, you're ready to go. Keep in mind before starting, though, that it's handy to have two additional items: a database to serve as a data source for your Web Service and a folder to hold the solution files created by the Web Services Toolkit. It will also be convenient if your database contains one or more stored procedures or UDFs that you want to expose via a Web Service. (You can also use Web Services to expose template queries, but covering this feature is outside the scope of this article.)

Launch the Web Services Toolkit by choosing Start | Programs | SQLXML 3.0 | Configure IIS Support. This opens the IIS Virtual Directory Management for SQLXML 3.0 Microsoft Management Console. Expand the controls in the console's left panel to select the Default Web Site and choose Action | New | Virtual Directory to start the process of creating a new virtual directory for your Web Service. The Default Web Site is the wwwroot folder in the Inetpub directory on the computer running IIS. The Action | New | Virtual Directory command opens the Properties dialog box for your Web Service.

When first starting out, you'll typically work with the first five of the six tabs in this dialog box (see Table 1). Clicking OK on any tab can save your settings to that point. You can return later to a saved virtual directory and either complete or update its settings. Simply right-click a virtual directory name and choose Properties from the context menu to reopen the Properties dialog box for a virtual directory.

Table 1. First five tabs of the Web Service Properties dialog box.

I used the tabs on the Web Services property dialog box to create a Web Service named SQLProDemo that points at the pubs database. Figure 1 shows the Virtual Names tab just before I clicked the Save button to commit my Web Service settings. The settings in Figure 1 create a soap object named soapy. Clicking Save enables Configure.

Next, I clicked Configure on the Virtual Names tab to select a database object for the Web Service to expose. Clicking Configure opens the "Soap virtual name configuration" dialog box, which enables the selection of a database object. I chose the reptq1 stored procedure in the pubs database; this stored procedure returns multiple resultsets. A Web Service can expose multiple resultsets as an array of objects—one object per resultset. Figure 2 shows the "Soap virtual name configuration" dialog box just after the selection of the reptq1 stored procedure. You can choose from a menu of objects available for exposing via a Web Service by clicking the ellipsis button (...) to the right of the SP/Template box. Clicking Save in the dialog box assigns the reptq1 Web method to the Web Service.

The reptq1 Web method returns its resultsets as a sequence of XML objects. As you can see, there are multiple configuration options. For example, you can choose to pass results back as dataset objects instead of XML objects.

After following the steps described, you'll see that the SQL Server 2000 Web Services Toolkit has created two files in the folder for the Web Service. Figure 1 shows that SQLProDemo is the name of our Web Service. The folder for the Web Service has the same name in the C:\Inetpub\wwwroot path on the CCS1 computer. The computer name will change according to the one running the SQL Server 2000 Web Services Toolkit. The two files in the folder have the same name with different extensions—SQLProDemo.ssc and SQLProDemo.wsdl. The .ssc file is a binary configuration file for the soap object. The .wsdl is a description of the SQLProDemo Web Service in WSDL (Web Service Description Language) format. WSDL, in turn, has an XML format.

Creating a Web Service client with VB.NET

There are several simple issues that you need to master in order to create a client for a Web Service with VB.NET. First, you need to add a reference to the Web Service in the client application. Second, you must declare and instantiate a proxy variable for the Web Service for which you add a reference. This proxy variable represents the Web Service in the client application. At this point, you can invoke any Web methods exposed by the Web Service from its proxy variable. If the Web method requires parameters, you can pass them as arguments when you invoke the Web method.

You can start to add a Web reference to a Windows Application project by choosing Project | Add Web Reference from the Visual Studio .NET IDE. This opens the Add Web Reference dialog box. Enter into the Address box an http:// prefix followed by the path to the IIS server with the virtual directory for the Web Service, the name of the Web Service folder, and the name of the soap object for the Web Service. Delimit the first and second as well as the second and third items with a forward slash (/). In our case, the soap object's name is soapy (see the Name box in Figure 1). Trail the soap object's name with a question mark delimiter (?) and the letters wsdl. Then, press Enter. After VS.NET connects to the Web Service, the dialog box displays a copy of the WSDL script for the Web Service in its left panel, and it enables the Add Reference button. Click this button to add the Web reference to the Windows Application project. Because CCS1 is the name of the computer running the Web Service Wizard, the Web reference has the name ccs1.

Listing 1 shows the VB.NET code that exists behind a Windows form for invoking the Web Service in my sample client application. A click to Button1 copies the return value from the first resultset for the reptq1 stored procedure in pubs to the Text property of Label1 on the application's Windows form. The proxy variable, xws1, points at the SQLProDemo Web Service. The reptq1 Web method for the xws1 proxy variable returns an Object array comprised of multiple resultsets in XML format. The array elements are zero-based. Therefore, the zero-eth element (response(0)) represents the first resultset. The OuterXML property for the result XMLElement object returns the resultset in XML format.

Listing 1. Button1_Click invokes the reptq1 Web method from the SQLProDemo Web Service.

  Private Sub Button1_Click(ByVal sender As _
System.Object, ByVal e As System.EventArgs) _
Handles Button1.Click
    'Declare proxy for Web service
    Dim xws1 As New ccs1.SQLProDemo()
    'Declare an object and an XMLElement
    Dim response As New Object()
    Dim result As System.Xml.XmlElement
    'Populate object and pass to xmlelement
    response = xws1.reptq1
    result = response(0)
    'Copy all of XMLElement object to label
    Label1.Text = result.OuterXml
End Sub

You can readily edit the procedure in the VB.NET listing to return other resultsets or subsets of the initial resultset. For example, replace response(0) with response(1) to return the second resultset. You can display just the first row of the initial resultset by referencing the FirstChild property before referencing the OuterXML property as follows:

  result.FirstChild.OuterXML

To return just the second row, replace the preceding expression with this one:

  result.FirstChild.NextSibling.OuterXML

Conclusions

The SQL Server 2000 Web Services Toolkit dramatically simplifies the creation of a Web Service based on SQL Server objects. All a SQL Server professional needs to do is make a few selections from a sequence of dialog boxes. Unfortunately, the Web Services Toolkit doesn't ship with SQL Server 2000. However, this article gives you the URLs for downloading the Web Services Toolkit and the SOAP 2.0 Toolkit, which you need in order to graphically create Web Services. In addition, there are several further requirements that this article details.

The steps described in this article work with either the initial version of the Web Services Toolkit (version 8.0.1529.1) or the updated version that's available with the Service Pack 1 release for SQLXML 3.0 (version 8.0.1915.0). Security issues are among those receiving expanded focus in the Service Pack 1 release. This article described the use of version 8.0.1915.0. [SQLXML 3.0 SP2 is now available from www.microsoft.com/downloads/details.aspx?FamilyId=4C8033A9-CF10-4E22-8004-477098A407AC& amp;displaylang=en.—Ed.]

Download 401DOBSON.ZIP

Sidebar: Resources

• Scott Swigart's 19-page article on SQLXML Managed Classes—https://msdn.microsoft.com/library/default.asp?url=/library/en-us/Dnsql2k/html/sqlxm l_intromanagedclasses.asp?frame=true
• Drew Minkin's 14-page article, "Inside the SQLXML Virtual Directory Structure"—https://msdn.microsoft.com/library/default.asp?url=/library/en-us/Dnsql2k/html/sql_insi devirtdirstruct.asp
• 321460 FIX: Patch Available for Script Injection with XML Tag and Unchecked Buffer in SQLXML ISAPI Extension Vulnerabilities
• 320833 FIX: Script Injection with XML Tag and Unchecked Buffer in SQLXML ISAPI Extension Vulnerabilities
• FIX: MDAC: Patch Available for Script Injection with XML Tag and Unchecked Buffer in SQLXML ISAPI Extension Vulnerabilities
• 324669 WebCast: Programming ADO SQLXML to Execute SQLXML Queries. Related: 323920 PRB: Run-Time Error When You Use SQLXML 2.0 with ADO to Execute an Updategram
• 324786 WebCast: Microsoft SQL Server 2000: How to Create and Consume Web Services Using SQLXML 3.0
• 321911 MS02-030: SQLXML Security Updates
• 810784 PRB: Error Message "HTTP Error 404 - File or directory not found" Occurs When You Access SQL Server 2000 with HTTP
• 316866 HOW TO: Import XML into SQL Server with the XML Bulk Load Component. Related: 316361 PRB: SQLXMLBulkLoad Error Message 80046000: "The System Cannot Find The Path Specified" and 316371 PRB: SQLXMLBulkLoad Error Message 80040E14: "File Could Not Be Opened" or "File Does Not Exist"

To find out more about SQL Server Professional and Pinnacle Publishing, visit their Web site at http://www.pinpub.com/

Note: This is not a Microsoft Corporation Web site. Microsoft is not responsible for its content.

This article is reproduced from the January 2004 issue of SQL Server Professional. Copyright 2004, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. SQL Server Professional is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-788-1900.