Configuring Web Queries In SQL Server
In this article, we will see how to configure SQL Server 2000 to accept queries over a web server. In order to complete the steps in this article, you will need to install SQLXML 3.0, which can be obtained from the Microsoft download center. In the example below, we will see how to configure support for the pubs database so that we can write URL-based queries.
-
In the SQLXML 3.0 program group, click Configure IIS Support.
-
Expand a server, and then click the Web site you want (for example, Default Web Site).
-
On the Action menu, point to New, and then click Virtual Directory. The property page for the new virtual directory is displayed on the screen.
-
On the General tab of the New Virtual Directory Properties dialog box, enter the name of the virtual directory (pubs) and the physical directory path (C:\Inetpub\Wwwroot\pubs). You can optionally use the browse (...) button to select the directory.
-
On the Security tab, select the appropriate authentication method.
-
On the Data Source tab, in the SQL Server box, enter the name of a server (for example, (local)) and, optionally, the name of an instance of SQL Server 2000 (if more than one instance is installed on the specified computer). In the Database box, enter Pubs as the name of the default database.
-
On the Settings tab, select the Allow URL queries, Allow template queries, Allow XPath, and Allow POST options.
-
On the Virtual Names tab, click
to create the virtual name of Template type. -
Enter template as the virtual name in the Name text.
-
Select template as the Type (virtual name type) from the drop-down list.
-
Specify the physical directory path that is associated with the virtual name (for example, C:\Inetpub\Wwwroot\pubs\template).
-
Click Save to save the virtual name of template type.
-
On the Virtual Names tab, click
to create the virtual name of Schema type. -
Enter schema as the virtual name in the Name text box.
-
Select schema as the Type (virtual name type) from the drop-down list.
-
Specify the physical directory path that is associated with the virtual name (for example, C:\Inetpub\Wwwroot\pubs\schema).
-
Click Save to save the virtual name of schema type.
-
On the Virtual Names tab, click
to create the virtual name of dbobject type. -
Enter dbobject as the virtual name in the Name text box.
-
Select dbobject as the Type (virtual name type) from the drop-down list.
-
Click Save to save the virtual name of dbobject type.
-
Click OK to save the settings.
This creates the virtual directory pubs. The queries that are specified by using this virtual directory are, by default, executed against the Pubs database.
To test the virtual directory, type the following in the browser and then press Enter:
http://<iisserver>/pubs?sql=SELECT au_id, au_lname, au_fname FROM authors FOR XML AUTO&root=root
After executing the above query in Internet Explorer, you will see the following output:
