Special Edition Using Microsoft Office Access 2003

Visual Basic 6.0 programmers can use the Microsoft SOAP Toolkit 3.0+ to generate SOAP wrappers and WSDL files for ActiveX components. Visual Studio .NET 1.0+ automates the process for creating ASP.NET XML Web services. Access 2003 developers have only one option for delivering data with Web services SQL Server 2000 stored procedures delivered from virtual directories you create with Microsoft's SQLXML 3.0+ Web release.

The first two versions of SQLXML extended SQL Server/MSDE 2000's built-in XML feature set with additional XML capabilities, such as updategrams and support for XSD schemas in addition to Microsoft's original (and proprietary) XML Data Reduced (XDR) annotated schemas. SQLXML 3.0 adds the Web service feature, which automates the process of exposing stored procedures as document/literal Web services. The following sections describe how to install SQLXML, and create and test a simple Web service from NorthwindSQL's Sales by Year stored procedure.

Note

SQLXML 3.0 SP 1 was current when this book was written. It's likely that Microsoft will continue to issue service packs and add features to SQLXML 3.0, at least until releasing the next version of SQL Server, codenamed "Yukon." Successive versions of SQLXML have proven to be backwardly compatible, so the following examples should apply to later SQLXML releases.

If you're running Windows Server 2003, SQLXML 3.0 requires running IIS 6.0+ in IIS 5 Emulation mode. Some applications, such as Windows SharePoint Services, won't run in IIS 5 Emulation mode. Microsoft probably will remove this SQLXML 3.0 limitation in future SQLXML Web releases.

Downloading and Installing SQLXML

SQLXML requires installation of the SQL Server Client Tools, which are included with the 120-day evaluation version of SQL Server 2000. You can obtain a CD-ROM containing the SQL Server 2000 Trial Software Release A at http://www.microsoft.com/sql/evaluation/trial/. Alternatively, you can purchase the SQL Server 2000 Developer Edition for $49. Neither version's Client Tools are licensed for production applications, but you don't need to use the Tools with SQLXML 3.0+ they need only be present during SQLXML 3.0 installation or removal.

For more information on the SQL Server Client Tools, see "Exporting Live Web Reports," p. 967.

Note

SQL Server Trial Version Release A includes SQL Server SP3, which immunizes the product from effects of the "Slammer" worm.

Caution

Don't install SQL Server 2000 from Developer or evaluation editions. Although you can add either edition as a named SQL Server instance, you take the chance of overwriting or disabling your current MSDE 2000 installation. During installation, select the Client Tools Only option.

If you're running Windows 2000, you must download and install the current version of the Microsoft SOAP Toolkit 3.0 when this book was written from MSDN to make SQLXML 3.0+'s Web service features operational. To find the latest version, go to http://msdn.microsoft.com/downloads/ and search for "SOAP Toolkit" in Downloads Only.

After you've installed the SQL Server 2000 Client Tools (and installed the SOAP Toolkit, if necessary), download and run the current version of SQLXML (Sqlxml.msi) from the link at http://www.microsoft.com/sql/. Installation adds a SQLXML 3.0 or later choice to your Programs menu with Configure IIS Support, SQLXML (3.0) Documentation, and SQLXML (3.0) Readme items.

Note

The IIS Virtual Directory Management for SQLXML 3.0 snap-in installs side-by-side with the Client Tools' IIS Virtual Directory Management. You must use SQLXML version 3.0 or later to take advantage of SQLXML's Web service features.

Creating a Simple Web Service

NorthwindSQL's Sales by Year stored procedure is a good candidate for an initial trial of SQLXML's capabilities, because it requires Beginning_Date and Ending_Date parameter values. The XSD schema for document/literal SOAP messages requires SQL Server datetime datatypes to conform to the XML Schema Part 2: Datatypes specification's dateTime datatype, which is based on the ISO 8601 standard.

Note

The ISO8601 format is CCYY-M M-D DThh:m m:ss[.##...]. CC represents the century, YY the year, MM the two-digit month and DD the two-digit day. hh, mm, and ss represent two-digit hours (24-hour clock), minutes, and seconds, which can have additional fractional digits (.##...).

An additional option is a trailing Universal Coordinated Time (UTC, Greenwich Mean Time, or Zulu) code (Z) or offset, expressed as {+|-}hh:mm. Thus the UTC dateTime value for 1:20:00 PM on June 20, 2003 is Pacific Standard Time, which is 8 hours behind UTC, is 2003-06-20T13:20:00-08:00.

To create a SalesByYearWS Web service, do the following:

1. Add a folder to contain the WSDL and supporting files for the service. For this example the location is \Inetpub\SalesByYear.

2. Choose Programs, SQLXML (3.0), Configure IIS Support to open the IIS Virtual Directory Management for SQLXML 3.0 snap-in.

3. Expand the nodes in the tree view, right-click the Default Web Site item, and choose New, Virtual Directory to open the General Page of the New Virtual Directory Properties dialog.

4. Replace New Virtual Directory with the virtual directory alias name SalesByYear for this example.

5. Type the folder name you added in step 1 in the Local Path dialog (see Figure 31.15). Click Apply.

   Figure 31.15. Specify the virtual directory alias name and the folder for the WSDL and supporting files in the General page of the New Virtual Directory Properties dialog.

6. Click the Security tab and select the Use Windows Integrated Authentication option to use your Administrator account for MSDE 2000. Click Apply.

   For alternative security choices, see "Setting Up the IIS Virtual Directory for the Database," p. 968.

7. Click the Data Source tab, accept (local) as the SQL Server instance, clear the Use Default Database for Current Login check box, and select NorthwindSQL as the active database. Click Apply.

8. Click the Settings tab, mark the Allow POST check box, and click Apply.

9. Click the Virtual Names tab, which selects the <New Virtual Name> item in the Defined Virtual Names list, type the virtual name SalesByYearWS in the Name text box, select SOAP in the Type list, specify the path to folder you added in step 1, and type the virtual name in the Web Service Name text box. Accept your computer's NetBIOS name as the domain name (see Figure 31.16).

   Figure 31.16. Specify the virtual name, type (soap), path to the virtual name folder, Web service name, and computer name on the Virtual Names page.

   Note

   Microsoft developers appear to have "SOAP schizophrenia." "SOAP," "Soap," and "soap" appear throughout SQLXML and other Microsoft Web service-related applications. SOAP is the official name of the protocol, so all instances of the name are capitalized in this and other chapters.

10. Click Save to save your virtual name settings and add the virtual name to the list, which enables the Edit, Delete, and Configure buttons.

11. Click Configure to open the SOAP Virtual Name Configuration dialog with <New Method Mapping> selected, accept the SP type option, and click the button to the right of the SP/Template text box to open the SOAP Stored Procedure Mapping dialog.

12. Select the Sales by Year stored procedure and click OK to return to the SOAP Virtual Name Configuration Dialog. Change the Method Name from Sales_by_Year to SalesByYear, and accept the default Raw and XML Objects options and Return Errors as SOAP Faults (see Figure 31.17).

    Figure 31.17. Select the stored procedure for the Web service, and specify the method name and structure of the SOAP response message in the Soap Virtual Name Configuration dialog.

13. Click save to add the SalesByYear method to the Methods list and click OK to return to the Virtual Names page. Click Apply and OK to close the dialog, save the ServiceName.wsdl and ServiceName.ssc (SQL Server configuration) files, and return to IIS Virtual Directory Management for SQLXML (3.0), which displays the new virtual directory in its list (see Figure 31.18). Close the snap-in.

    Figure 31.18. IIS Virtual Directory Management for SQLXML 3.0 adds a 3 icon to the IIS virtual directories you create. The icon is important because it distinguishes SQLXML virtual directories from those you create with the snap-in that's included with the SQL Server 2000 Client Tools.

<?xml version="1.0"?> <wsdl:definitions name="SalesByYearWS" ...> <!-- Namespace and schema definitions omitted for brevity --> <wsdl:message name="SalesByYearIn"> <wsdl:part name="parameters" element="tns:SalesByYear"/> </wsdl:message> <wsdl:message name="SalesByYearOut"> <wsdl:part name="parameters" element="tns:SalesByYearResponse"/> </wsdl:message> <wsdl:portType name="SXSPort"> <wsdl:operation name="SalesByYear"> <wsdl:input message="tns:SalesByYearIn"/> <wsdl:output message="tns:SalesByYearOut"/> </wsdl:operation> </wsdl:portType> <wsdl:binding name="SXSBinding" type="tns:SXSPort"> <soap:binding transport="http://schemas.xmlsoap.org/soap/http"/> <wsdl:operation name="SalesByYear"> <soap:operation soapAction="http://OAKLEAF-XP1/SalesByYear/SalesByYearWS/SalesByYear" /> <wsdl:input> <soap:body use="literal"/> </wsdl:input> <wsdl:output> <soap:body use="literal"/> </wsdl:output> </wsdl:operation> </wsdl:binding> <wsdl:service name="SalesByYearWS"> <wsdl:port name="SXSPort" binding="tns:SXSBinding"> <soap:address location="http://OAKLEAF-XP1/SalesByYear/SalesByYearWS"/> </wsdl:port> </wsdl:service> </wsdl:definitions>

The most important element in SalesByYearWS.wsdl is the value of the location attribute, which combines the domain, virtual directory, and Web service names you specify to create a URL that points to the service's virtual name and location of the WSDL document.

SQLXML Web services require a ServiceName.ssc file to map stored procedure parameters, their datatypes, direction, and other attributes to Web service input/output parameters. Following is the reformatted content of SalesByYearWS.ssc:

<?xml version="1.0"?> <sxs:methods name="SalesByYearWS" domain="OAKLEAF-XP1" url="http://OAKLEAF-XP1/SalesByYear/SalesByYearWS" xmlns:sxs="http://schemas.microsoft.com/SQLServer/2001/12/SOAPxml"> <sxs:method name="SalesByYear" type="storedproc" spname="[Sales by Year]" format="raw" output="xmlobject" faults="true"> <parameter name="@RETURN_VALUE" type="3" paramSize="4" precision="10" input="false" output="true" is-="false"/> <parameter name="@Beginning_Date" type="135" paramSize="16" precision="23" scale="3" input="true" output="false" is-="false"/> <parameter name="@Ending_Date" type="135" paramSize="16" precision="23" scale="3" input="true" output="false" is-="false"/> </sxs:method> </sxs:methods>

SalesByYearWS.ssc defined one output and two input SQL Server parameters. SQLXML3 ignores the @RETURN_VALUE integer (type="3") output parameter, which is present for backward compatibility with prior SQLXML3 versions. The @Beginning_Date and @Ending_Date input parameters require SQL Server datetime (type="135") data types. Unfortunately, SQL Server Books Online doesn't include a cross reference between numeric type values and SQL Server datatypes.

Testing the Service with .NET WebService Studio

It's usually not worth the effort to create an Access consumer application for a test service, especially when you can verify operability and read the SOAP request and response messages with WSS.

Follow these steps to test drive SalesByYearWS's SalesByYear Web method with Windows (NTLM) authentication:

1. Open WSS, type http://computername/salesbyyear/salesbyyearws?wsdl" in the WSDL EndPoint text box, and click get to generate the Web service client proxy.

   Tip

   If you encounter an error with the preceding URL, click Continue to dismiss the error dialog, click Browse, and select the SalesByYearWS.wsdl file in your \IntetPub\SalesByYearSales folder.

2. Click the Request/Response tab and set the AllowRedirect, KeepAlive, and UseDefaultCredential HTTP properties to True (look ahead to Figure 31.20).

3. Return to the Invoke page, select the Beginning_Date parameter, and type 1/1/1998 as its value. Do the same for Ending_Date, but substitute a later date, and click Invoke to consume the Web service.

   If you encounter an error when you attempt to invoke your local Web service, see the "Problems with WSS Security Settings" topic of the "Troubleshooting" section near the end of the chapter.

4. Scroll the Output list to reveal the four child node values of the row node: ShippedDate, OrderID, Subtotal, and Year (see Figure 31.19).

   Figure 31.19. SalesByYearWS's SalesByYear method returns a hierarchy of XMLDOMNodes, which is similar in structure to that created by Alpha's GetTop### methods.

5. Click Request/Response to read the SOAP request and response messages (see Figure 31.20). WSS transforms the parameter's date value to XSD dateTime values, which have seven fractional time digits and the UTC offset for your time zone. (Your computer's time zone setting establishes the offset value.)

   Figure 31.20. WSS's display of SOAP request and response messages illustrates the full and minimal ISO 8601 dateTime formats.