Use SQLXML 3.0 to expose stored procedures as web services
The latest technology is a web service. The basic elements of the terms that appear in all markets, Web Services, WSDLs, general descriptions, discovery, and integrated (UDDI), and other terms are both XML and Internet.
The web service is used to establish a loosely connected application and implement interoperability. A loose connection app allows you to reconfigure, redeploy or reposition its implementation without affecting related applications. Interoperability involves the establishment of applications that can be used on any platform that allows Web services. Exposing SQL Server Give the application via a web service that enables you to modify the structure and location of the database without affecting the application. In addition, the Web service allows SQL Server to interact with more programming environments and platforms.
In order to achieve interoperability, developers need to establish web services in a set of open industrial standards and protocols. XML is the basis of most of these standards, providing a description and data representation of the platform. The SOAP established on XML provides a standard-based approach to send data to the application and receive data from the application, representing the use of HTTP. WSDL describes the data types used by location, methods, parameters, and web services. UDDI provides an interface for a directory for registering a Web service or finding other web services required. By combining these standard techniques and T-SQL programming, you can implement the SQL Server stored procedure as a web service.
The focus of SQL Server 2000 Web Release 3 (SQLXML 3.0) is web service (you can pass link
Http://microsoft.com/sql/default.asp Download SQLXML 3.0). SQLXML 3.0 allows you to select stored procedures from the database and select XML templates from the virtual directory to expose them as a web service. Because you can call a stored procedure through web services and traditional methods, such as T-SQL EXEC statements, OLE DB, ADO, and ODBCs, you can maximize these components. Simply put, SQLXML 3.0 allows you to extend the dedicated technology in the database to the Web service without having to learn new languages or tools. Let's take a look at how to transform the stored procedure into a web service.
Configure the virtual directory. The first step in exposing the stored procedure to the Web service is to select the Microsoft IIS Virtual Directory Manager from the Configure Iis Support generated by the SQLXML 3.0. To create a virtual directory, select the web server from the tree view on the left, then expand this view and click the default Web site. Right-click any area in the right panel, select Context-"New-" Virtual Directory to display a New Virtual Directory PROPERTIES dialog. On the Security and Data Source page, enter the SQL Server login license that can access the Northwind sample database. Note that your license for the directory selected on the General page must give you the default user authorization for IIS configuration on the Security and Data Source page (see the details of the IIS document looking for configuration). To avoid licensed issues, choose INETPUB / WWWWROOT for your virtual directory, and IIS default users automatically have access to the data below INETPUB / WWWROOT. Next, on the Settings page, select the Allow POST option, so that the virtual directory can accept the HTTP POST request. Click "Apply".
Configure the virtual name. After configuring the virtual directory, click the "Virtual Name" page on the New Virtual Directory Properties dialog. Select a new virtual name from the virtual name list, select a name for the virtual name, then set it to SOAP. Listing 1 shows the VBScript code that puts the SOAP string as a virtual name. Enter a path to the virtual name. The path contains the WSDL file output from the IIS Virtual Directory Manager when you save your modification to the root directory. I usually select ('.'), Which select the same directory as the virtual directory. You can receive the default value of the remaining fields in the dialog. Click "Save". When you save your virtual name, SQLXML 3.0 writes two files to your specified virtual name directory, extension .wsdl. Other file extensions are .ssc, the XML description of the stored procedures used by the IIS Virtual Directory Manager. Now you choose to expose the stored procedure as a web service method. In the examples herein, I use the OrderSForCustomer stored procedure shown in Listing 2. This stored procedure returns an order collection that specifies the customer ID (as a parameter transfer). Before making the next step, you must use the query analyzer (Query Analyzer) to install this stored procedure in the Northwind sample database. Listing 1: Calling a stored procedure VBScript code SUB Main () DIM XMLHTTP SET XMLHTTP = CreateObject ("msxml2.xmlhttp.4.0") DIM Request request = " "& _"
List 2: establishing OrdersForCustomer code stored procedure CREATE PROCEDURE OrdersForCustomer @CID nvarchar (50) ASSELECT * FROM Customers, OrdersWHERE Customers.CustomerID = @CIDAND Customers.CustomerID = Orders.CustomerIDGO
Establish a web service. On the "Virtual Name" page of the New Virtual Directory Properties dialog box, select the virtual name just established, then click Configure. This shows the SOAP Virtual Name Configuration dialog (shown in Figure 1). In this dialog, select the stored procedure you want to expose as a web service method. In order to select a stored procedure, click ("..."), it creates a list of stored procedures available in a database you configure virtual directory access. Do not select the stored procedure containing the for XML clause query. Instead, you must choose the stored procedure that returns the standard result collection. SQLXML 3.0 is desired to store procedures to return the result set of output parameters or standard results. The result is then established in the intermediate layer to XML. Selecting the stored procedure for returning a standard result set enables all flexibility that can be used for a WEB service or a traditional programming method (such as T-SQL). When you establish an XML query result in the middle layer, be careful to avoid restrictions on SQLXML 3.0 (you can view the SQLXML 3.0 online document to view these restrictions). After you select the ORDERSFORCUSTOMER store, click "OK" (OK), then click the "Save" button of the SOAP Virtual Name Configuration dialog box. This way you have established the first web service. You can check the WSDL in the directory selected by the virtual name using any text editor. The WSDL file contains the corresponding method definition of the WEB service you selected. Figure 1: SOAP Virtual Name Configuration dialog
In order to test the newly established Web service, use the VBScript code displayed in the list 1. This code establishes a SOAP message calling the web service, then sending the SOAP request to the web service using the XMLHTTP object. SOAP results are displayed in a message box. This client briefly demonstrates SOAP to be exchanged between VBScript and SQLXML 3.0. However, it also shows that stored procedure calls can be used through the use of SOAP cross-platform interaction on HTTP, which provides loose connections to the stored procedure through descriptions in the WSDL and SCC files.
----------------
This article is translated from:
"CREANG A Web Service - Use Sqlxml 3.0 to Expose Your Stored Procedures As Web Services"
