Use SQLXML 3.0 to expose your stored procedures as Web services
Editor's Note: Beginning this month, Exploring XML is transitioning from a question-and-answer format to a subject-based column, complemented by occasional questions and answers. You'll still find the same in-depth XML information, including plenty of code samples and practical information about how to use SQL Server XML technologies to build applications. Please submit your suggestions for future column topics and your questions to xmlquestions@sqlmag.com.
The latest technology buzz is Web services. Terms such as Simple Object Access Protocol (SOAP), Web Services Description Language (WSDL), and Universal Description, Discovery, and Integration (UDDI) now appear in almost all marketing materials about XML and the Internet. But how are Web services relevant to you as a SQL Server professional?
Web services are about building loosely coupled applications and achieving interoperability. A loosely coupled application lets you reconfigure, redeploy, or relocate the implementation without affecting dependent applications. Interoperability refers to building applications that you can use from any Web services—enabled platform. Exposing SQL Server through Web services gives applications a loosely coupled interface that lets you modify the structure and location of your database without affecting the application. Additionally, Web services let SQL Server interoperate with more programming environments and platforms.
To achieve interoperability, developers established Web services on a set of open industry-standard protocols and formats. XML is the basis for many of these standards, providing a platform-independent description and representation of data. SOAP builds on XML to provide a standards-based way to send data to and receive data from an application, typically by using HTTP. WSDL describes the location, methods, parameters, and data types that a Web service uses. UDDI provides an interface to a directory in which you can register your Web service or find other Web services you might require. By combining these standard technologies and T-SQL programming, you can implement SQL Server stored procedures as Web services.
The focus of SQL Server 2000 Web Release 3 (SQLXML 3.0) is Web services. (You can download SQLXML 3.0 at http://microsoft.com/sql/default.asp.) SQLXML 3.0 lets you select stored procedures from your database and XML templates from your virtual directory to expose as a Web service's methods. Because you can now invoke stored procedures through Web services as well as through traditional methods— such as T-SQL EXEC statements, OLE DB, ADO, and ODBC—you get maximum reuse of these components. In short, SQLXML 3.0 lets you extend your expertise in database programming to the new world of Web services without learning a new language or new tools. Let's see how to turn stored procedures into Web services.
Configuring your virtual directory. The first step in exposing a stored procedure as a Web service is to start the Microsoft IIS Virtual Directory Manager from the Configure IIS Support menu item that installs with SQLXML 3.0. To create a new virtual directory, select your Web server from the left tree view, then expand the view and click Default Web Site. Right-click in any open area on the right pane. Select Context, New, Virtual Directory to display the New Virtual Directory Properties dialog box. On the tabs of this dialog box, you configure the virtual directory to access your database. On the General tab, name your virtual directory June 2002. Then, select a local directory on your system. On the Security and Data Source tabs, enter a SQL Server login with permissions to access the Northwind sample database, which you select on the same tab. Note that permissions on the directory you selected on the General tab must grant permissions to the default user you configured for IIS on the Security and Data Source tabs. (See IIS documentation for configuration details.) To avoid permission problems, choose a subdirectory of the Inetpub\wwwroot directory for your virtual directory; the default user for IIS automatically has permissions to access data below Inetpub\wwwroot. Next, on the Settings tab, select the Allow POST option so that the virtual directory will accept HTTP POST requests. Click Apply.