SQL 2000 and Web Services (XML and SQL part 11)

In the last article of this series, we will focus on Web Services and their
implementation in the SQL Server 2000 environment with the help of the SQLXML
component. In particular, we will look into a non-programmatic way of
publishing SQL Server 2000 stored procedures, user-defined functions and XML
templates as Web Services. Note that this functionality requires the SQLXML
component version 3.0 (currently at Service Pack 1 level), downloadable from the
Microsoft Web Site.

Web Services constitute one of the most significant benefits of Microsoft
.NET technology. The majority of software development these days is geared
towards distributed environments. This not only benefits scalability and
availability but also speeds up development time, due to increased reusability.
A client application can take advantage of pre-fabricated components residing
on multiple, redundant servers, eliminating the need for implementing the same
functionality locally. The idea is not new, however. In the past, a
client-server session was frequently limited by both the operating system and
development platform, as well as communication protocols. With .NET Web
Services, these limitations have been removed by introducing solutions based on
open standards such as XML and HTTP.

HTTP has been selected as the transport protocol for Web Services due to its
universal nature. It is undoubtedly the protocol most commonly allowed to pass
corporate firewalls, which allows sharing Web Services beyond intranet
environments. While HTTP GET and HTTP POST requests are still available,
typically, messages between a client and a Web Services server are exchanged
via SOAP (an acronym for Simple Object Access Protocol). SOAP messages are
XML-formatted documents that specify the schema defining their format and
contain reference to a method to be executed as well its parameters or results
(depending on whether the message is a SOAP request and SOAP response). XML is
also used by Web Services Description Language (WSDL) for publishing Web
Services in order to allow prospective clients to determine what methods are
available and what parameters are required for their execution.

Starting with SQLXML 3.0, it is possible to create a Web Service by defining
a Virtual Directory of SOAP type, which, in turn, results in generating a WSDL
file describing functionality of stored procedures, XML templates or
user-defined functions. Once the file is available, any SOAP aware client can
post a properly formatted SOAP request for services provided by the Web Service
created this way. The Web server running the SQLXML component forwards the
request to the data source and returns the results in the form of a SOAP
response to the client. In the case of an XML Template, the resulting XML
document is simply returned by the Web Service, in the case of stored
procedures or user-defined functions, the SQLXML component is responsible for
converting relational data to XML format and returning both XML data as well as
output parameters.

Note, that in order to take advantage of Web Services, you will need to
create a Web Services client. This can be accomplished with on .NET development
platform (Visual Studio .NET greatly simplifies this task), but it is also
possible with COM based applications, by employing SOAP Toolkit available for
download from the
Microsoft Web site.
For details on developing both types of clients, refer
to the "Writing Client Applications" topic of the SQLXML 3.0 Service
Pack 1 help file (included with the SQLXML component). In this article, we will
provide a description of the procedure necessary to set up Web Services:

  • Start by creating a new virtual directory representing the target
    SQL Server 2000 database (we will be using Northwind). The required steps were
    outlined in one of
    our earlier articles
    .

  • Ensure that Allow Post is checked on the Settings tab of the
    Virtual Directory properties dialog box. This is necessary in order for SOAP
    requests from Web Services clients to be accepted.

  • From the Virtual Names tab of the Northwind Properties dialog
    box, create a virtual name of the soap type. You will need to provide the
    following values:

    • Name – name assigned to the virtual directory (NorthwindProcedures
      in our case),

    • Type – must be set to soap,

    • Path – physical path to the folder containing files used by
      virtual directory (c:InetPubwwwrootNorthwind in our case)

    • Web Service Name – name assigned to the Web service used in the
      WSDL XML document (by default set to the same value as the name assigned to
      virtual directory). This name is used by clients to contact the Web Service. We
      will leave it as NorthwindProcedures.

    • Domain Name – namespace used for the Web service (included in the
      WSDL document). This value is set by default to the hostname of the server
      hosting the Web server. You might want to replace it with the fully qualified
      name (including your DNS domain name) to ensure its uniqueness and make it
      easily identifiable when operating in an Internet environment. In our case, we
      will set it to WebServer.databasejournal.com
  • Once you saved the new virtual name of soap type, you need to
    configure it. After clicking on the Configure button on the Virtual Name tab,
    you will be presented with the Virtual Name configuration dialog box.

  • In the Edit/New mapping section, you can choose between SP
    (stored procedures and user-defined functions) and Template (XML templates). The
    listing you will see after clicking on the (…) button will depend on this
    initial selection.

  • Selecting a stored procedure or a template automatically sets the
    Method name text box to match the procedure name. This is the name that will be
    used by the Web Services client to invoke the underlying stored procedure, template
    or user-defined function.

  • If you selected a stored procedure, you will also need to specify
    how its results will be converted to XML format. This involves specifying
    output formatting (Raw or Nested) and response type (XML objects, Dataset objects,
    or Single dataset). Raw and Nested options correspond to the described earlier
    RAW and NESTED modes of the SELECT queries. XML objects designates the response
    type in which a separate XML element is created for each XML fragment returned
    from the stored procedure. Dataset objects response type results in each
    dataset returned in a separate element. Finally, single dataset option produces
    a single element for the entire result set (so it is not intended for multiple
    result sets). Note that methods mapped to XML templates always return XML
    objects.

  • Finally, the "Return error as soap faults" causes
    errors to be returned in the form of SOAP messages to clients (which can be
    used for troubleshooting or error detection by SOAP clients).

For each Web Service you configure, the SQLXML 3.0 component creates two
files residing in the folder corresponding to the Web Services virtual
directory (defined by the Path parameter on the Virtual Name tab of the virtual
directory Properties dialog box). Both files are given the same name as the Web
Service Name you assigned previously. The first one (with extension .wsdl)
contains the Web Services Description Language document, the second one (with
extension .ssc) is the Web Services configuration file. You can retrieve the
content of the WSDL file by typing the following in the web browser Address
box:

http://WebServer.databasejournal.com/Northwind/NorthwindProcedures?wsdl

As you can see, building Web Services with SQLXML 3.0 is straightforward and
quick, giving you access to stored procedures, user-defined functions or XML
templates. All that is required is to properly configure SQLXML properties and
develop a Web Services client.

This article concludes our series, which was intended to give you overview
of the most popular XML related features in SQL Server 2000.

»


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles