SQL Server 2000 provides tight integration with IIS via an ISAPI filter called sqlisapi.dll that IIS calls on behalf of SQL Server to execute the queries you specify in the text of a URL.
If you haven't already done so, please follow the instructions in the first section of this chapter to create the virtual directory and virtual names you will use to access the Northwind database.
The simplest way to execute T-SQL commands using a URL query is to specify a select statement in the URL. Open Internet Explorer and type the following, substituting your Web server name for < myserver > :
http://< myserver >/NorthwindVdir?sql=SELECT EmployeeID FROM Employees FOR XML AUTO&root=myEmployees
The syntax of this address is as follows :
http://<myserver>/ myVirtualDirectory ?sql=[ sql statements ]&root=[ rootElementName ]
myVirtualDirectory is the name of a virtual directory and the ? character specifies that what follows is a query string ”a list of name-value pairs of the form name=value&name=value. These pairs are the named parameters that are passed to the virtual directory or template file for processing.
In this example, the value of sql is one or more T-SQL statements that return XML using FOR XML . root tells the URL query processor to enclose the resulting document fragment in a root element named rootElementName. The results are shown in Figure 41.10.
Figure 41.10. Using a virtual directory to execute a URL query.
You can also execute stored procedures that return XML by using the EXEC keyword in the sql parameter. To try this, open Query Analyzer and, using the Object Browser, navigate to and expand the Stored Procedures node under the Northwind database. Right-click dbo.CustOrderHist , navigate to Script Object to New Window As, and select Alter (see Figure 41.11).
Figure 41.11. Altering CustOrderHist stored procedure with FOR XML RAW .
Add a FOR XML RAW clause to the select statement (after GROUP BY ). Press F5 to save your changes. Then test this URL in Internet Explorer by typing the following address:
http://<myserver>/NorthwindVdir?sql=exec CustOrderHist @CustomerID='ANTON' &root=OrderHistory
The resulting well- formed XML document is found in Figure 41.12.
Figure 41.12. Executing a stored procedure from a URL query.
Allowing URL queries opens up a potential security hole in your system you might not want. Not to give you any bad ideas, but how easy would it be to run a query like sql=DELETE FROM Customers ? For better security, use XML templates.