Home > Articles

chap16_0789724499

  • Print
  • + Share This

HTTP Access to SQL Server 2000

With SQL Server 2000, you can access your data from anywhere on the Internet by using the HTTP protocol. This is especially useful when you need to cross firewalls, because the HTTP protocol uses port 80, which does not present any problem when working through firewalls.

SQL Server can produce static HTML pages with the Web Assistant Wizard. You can use this wizard to refresh the HTML page whenever the underlying data changes. However, this way of publishing SQL Server data on the Internet is not very flexible, because it is limited to specific reports.

You can use Internet Information Server with the SQL Internet Server Application Programming Interface (ISAPI) filter to send queries to SQL Server. IIS sends the results back to the client over HTTP.

The process, simplified, works as follows:

  1. The client connects to a special virtual directory in IIS defined for SQL Server access, specifying a query to execute in the HTTP request.

  2. The SQLISAPI filter sends the request to SQL Server through OLE DB using the FOR XML clause

  3. SQL Server returns the results to IIS in XML format.

  4. IIS returns the results to the client application through HTTP.

You can send requests to SQL Server through HTTP in different ways:

  • URL queries, where the query is embedded in the URL call itself. This option should be used only in testing scenarios.

  • XML template files, where the query is defined in pre-built files, which provides a more secure access than with URL queries.

  • XPath queries against schemas, to validate queries containing XPath language statements.

  • POST method, to be used directly from any Web page.

  • Updategrams, to insert, delete, or modify SQL Server data by XML updategrams.

CAUTION

Posting queries or templates directly through HTTP represents security problems, because users can send any valid Transact-SQL query using this method, as long as they can connect to the virtual directory.

In a production Internet environment, it is recommended to disable POST queries, restricting the HTTP access to using template files only, because in this way you restrict the queries that users can send through HTTP.

Note

You must install XML for SQL Server Web Release to be able to use updategrams in SQL Server 2000.

You can download this upgrade from http://msdn.microsoft.com/downloads/default.asp?URL=/code/sample.asp?url=/msdn-files/027/001/554/msdncompositedoc.xml

This section teaches you how to create an IIS virtual directory for SQL Server and send queries through HTTP using URL queries and templates.

For more information about this interesting topic, you can read the section "Accessing SQL Server Using HTTP" in Books Online, and the "XML for SQL Documentation" installed with the XML for SQL Server 2000 Web Release.

Creating a SQL Server Virtual Directory for IIS

The first step to provide HTTP access to SQL Server is to configure a SQL Server virtual directory in IIS.

When you install SQL Server 2000, you can find a menu called Configure SQL XML Support in IIS. If you click on this menu, you start the Microsoft Management Console with the IIS Virtual Directory Management for SQL Server applet.

Select your server, expand its tree, and right-click on the Default Web Site node to display the context menu. On the context menu, choose New, Virtual Directory to start the IIS Virtual Directory Management Utility. Write the name of the virtual directory and the physical path, located in the General tab, and you will see something similar to Figure 16.9. To follow the examples of this section, the virtual directory is called SQLXML.

Figure 16.9 The new virtual directory properties.

Click on the Security tab to specify how the users will be authenticated. You have the following options:

  • Always log on as, to specify a single account for all HTTP access to SQL server. Make sure you don't use an account with administrative privileges in SQL Server when using this option.

  • Use Windows Integrated Authentication, which uses the Microsoft Windows NT challenge/response (Windows NT 4.0) or Windows Integrated Authentication (Microsoft Windows 2000) IIS authentication method.

  • Use Basic Authentication (Clear Text) to SQL Server account, to prompt for a SQL Server username and password.

In this case, select the Use Windows Integrated Authentication option. You will see the situation exposed in Figure 16.10.

Figure 16.10 Specify the authentication mode to access SQL data through HTTP.

Note

To provide different access security modes, you can create different virtual directories to the same server. From IIS you can manage access security to each one of these virtual directories.

Click on the Data Source tab to select the target server and the default database, as shown in Figure 16.11.

Figure 16.11 Specify the target SQL Server instance and the default database.

Note

To access multiple servers, create one virtual directory for each one.

Click on the Settings tab to select the access method. In this case, select Allow URL queries, Allow XPath, and Allow template queries, as shown in Figure 16.12.

Figure 16.12 Configure the access method to the SQL Server data.

To test the virtual directory you just configured, open Internet Explorer, and type the following URL in the address line:

http://localhost/sqlxml?sql=SELECT+CategoryName+
         FROM+Categories+As+Category+
         FOR+XML+AUTO&root=Categories

You will see what is shown in Figure 16.13.

Note

In URL queries, type a + symbol instead of spaces.

Figure 16.13 Results of a URL Query through an Internet Information Server virtual directory for SQL Server.

Using SQL Server Data From HTTP

In the preceding section, you learned how to set up a virtual directory for SQL Server in Internet Information Server. You tested the virtual directory by sending a simple URL query.

You can use this virtual directory to send any kind of query to SQL Server. However, as you might imagine, this can be tedious, error-prone, and a possible security risk, because users can send any query through URL.

You can create XML template files with predefined queries. An XML template is an XML document that contains one or more SQL statements and XPath queries.

You can create your own first XML template now. Create a folder called Templates anywhere in the server hard disk. Open Notepad and create a new file called Categories.xml, stored in the physical path of the virtual directory you created in the preceding section. Write in this file the code included in Listing 16.23.

Listing 16.23—An XML Template to Show Categories

<?xml version="1.0"?>
<Categories xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
 <sql:query>
   SELECT CategoryName
   FROM Categories AS Category
   FOR XML AUTO
 </sql:query>
</Categories>

Now open the IIS Virtual Directory Management for SQL Server and open the virtual directory you created in the preceding section. Go to the Virtual Names path, and create a new virtual name, called Templates, pointing to the Templates directory you just created, and specify a type template for this virtual directory. Figure 16.14 shows the Virtual Names tab after creating this virtual name.

Figure 16.14 This SQL virtual directory contains a template virtual name.

Note

You don't need to create the template folder in the same directory as the virtual directory. However, if the template files are not stored in a virtual name folder of type template, IIS will not recognize them as valid XML templates.

Now you can open Internet Explorer and access the following URL:

http://localhost/sqlxml/templates/Categories.xml

You will see the familiar categories list in XML format.

In this way, you can have predefined queries saved as templates, and the users don't need to know how the queries are built.

To do something more useful, you can try to use parameters to select specific information about customers, their orders, and the products included in their orders. To do so, you can write the code included in Listing 16.24 and save it in the file CustomersOrders.XML in the Templates directory you created earlier.

Listing 16.24—An XML Template to Show Customers and Their Orders

<?xml version="1.0"?>
<Customers xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
 <sql:header>
   <sql:param name="Customer">NULL</sql:param>
   <sql:param name="Product">NULL</sql:param>
 </sql:header>
 <sql:query>
   SELECT CompanyName,
     OrderDate,
     ProductName,
     Quantity,
     Item.
     UnitPrice
   FROM Customers AS Customer
     JOIN Orders AS [Order]
       ON [Order].CustomerID = Customer.CustomerID
     JOIN [Order Details] AS Item
       ON Item.OrderID = [Order].OrderID
     JOIN Products AS Product
       ON Product.ProductID = Item.ProductID
   WHERE CompanyName LIKE
     CASE @Customer
       WHEN 'NULL' THEN '%'
       ELSE @Customer + '%'
       END
   AND ProductName LIKE
     CASE @Product
       WHEN 'NULL' THEN '%'
       ELSE @Product + '%'
       END
   ORDER BY CompanyName, OrderDate, ProductName
   FOR XML AUTO
 </sql:query>
</Customers>

Looking at the code in Listing 16.24, you can identify a new section at the beginning of the file:

 <sql:header>
   <sql:param name="Customer">NULL</sql:param>
   <sql:param name="Product">NULL</sql:param>
 </sql:header>

That section defines the parameters to use. These parameters work in a similar way to the parameters in stored procedures or user-defined functions. In this case you declared two parameters: Customer and Product. For both parameters you declared NULL as their default value. Note that in this case it is not the SQL NULL value, but the string 'NULL', which is not considered in the same way by SQL Server.

This is the reason why the WHERE clause looks that uncommon:

   WHERE CompanyName LIKE
     CASE @Customer
       WHEN 'NULL' THEN '%'
       ELSE @Customer + '%'
       END
   AND ProductName LIKE
     CASE @Product
       WHEN 'NULL' THEN '%'
       ELSE @Product + '%'
       END

You can test this template by writing the following URL in Internet Explorer:

http://localhost/sqlxml/templates/CustomersOrders.xml

In this example, you didn't use the parameters, so IIS returns a big XML document, including every order made to every customer.

To reduce the scope, write the following URLs, and look at the results in Internet Explorer:

http://localhost/sqlxml/templates/CustomersOrders.xml?Customer=Alfreds
http://localhost/sqlxml/templates/CustomersOrders.xml?Customer=Alf&Product=Ra
http://localhost/sqlxml/templates/CustomersOrders.xml?Product=Ravioli

However, the way Listing 16.24 sends the query is not efficient. Listing 16.25 shows a longer but more efficient Transact-SQL code, because SQL Server can use indexes more efficiently, according to the parameters sent. The only difference between these two examples is that the query in Listing 16.25 splits the query into four individual queries:

  • Retrieve information about all orders when the user does not specify any of the parameters (Product and Customer are both NULL).

  • Retrieve only orders information related to the products whose name starts with the value specified in the parameter Product (Customer is NULL)

  • Retrieve only orders information related to the customers whose name starts with the value specified in the parameter Customer (Product is NULL)

  • Retrieve only orders information related to products with names that begin with the value specified in the parameter Product and customers with names that begin with the value specified in the parameter Customer.

Listing 16.25—A More Efficient XML Template to Show Customers and Their Orders

<?xml version="1.0"?>
<Customers xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
 <sql:header>
   <sql:param name="Customer">NULL</sql:param>
   <sql:param name="Product">NULL</sql:param>
 </sql:header>
 <sql:query>
   IF @Customer = 'NULL'
     IF @Product = 'NULL'
       SELECT CompanyName,
         OrderDate,
         ProductName,
         Quantity,
         Item.
         UnitPrice
       FROM Customers AS Customer
         JOIN Orders AS [Order]
           ON [Order].CustomerID = Customer.CustomerID
         JOIN [Order Details] AS Item
           ON Item.OrderID = [Order].OrderID
         JOIN Products AS Product
           ON Product.ProductID = Item.ProductID
       ORDER BY CompanyName, OrderDate, ProductName
       FOR XML AUTO
     ELSE
       SELECT CompanyName,
         OrderDate,
         ProductName,
         Quantity,
         Item.
         UnitPrice
       FROM Customers AS Customer
         JOIN Orders AS [Order]
           ON [Order].CustomerID = Customer.CustomerID
         JOIN [Order Details] AS Item
           ON Item.OrderID = [Order].OrderID
         JOIN Products AS Product
           ON Product.ProductID = Item.ProductID
       WHERE ProductName LIKE @Product + '%'
       ORDER BY CompanyName, OrderDate, ProductName
       FOR XML AUTO
   ELSE
     IF @Product = 'NULL'
       SELECT CompanyName,
         OrderDate,
         ProductName,
         Quantity,
         Item.
         UnitPrice
       FROM Customers AS Customer
         JOIN Orders AS [Order]
           ON [Order].CustomerID = Customer.CustomerID
         JOIN [Order Details] AS Item
           ON Item.OrderID = [Order].OrderID
         JOIN Products AS Product
           ON Product.ProductID = Item.ProductID
       WHERE CompanyName LIKE @Customer + '%'
       ORDER BY CompanyName, OrderDate, ProductName
       FOR XML AUTO
     ELSE
       SELECT CompanyName,
         OrderDate,
         ProductName,
         Quantity,
         Item.
         UnitPrice
       FROM Customers AS Customer
         JOIN Orders AS [Order]
           ON [Order].CustomerID = Customer.CustomerID
         JOIN [Order Details] AS Item
           ON Item.OrderID = [Order].OrderID
         JOIN Products AS Product
           ON Product.ProductID = Item.ProductID
       WHERE ProductName LIKE @Product + '%'
       AND CompanyName LIKE @Customer + '%'
       ORDER BY CompanyName, OrderDate, ProductName
       FOR XML AUTO
 </sql:query>
</Customers>

As you have seen in Listing 16.25, you are not limited to a single query in a template, but a complete batch including several statements, as long as you use FOR XML in every SELECT statement.

  • + Share This
  • 🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020