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
-
Name – name assigned to the virtual directory (NorthwindProcedures
-
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.
