I want to return an XML stream from a SQL 2000 database using a URL query.

I have been trying to get a simple M$ Northwind example to work on a Windows 2003 server but I can't get it to work. I can get the simple example to work on a Win2000 server though.

I set up the required virtual directory following the steps located here (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsql/ac_xml1_2xvd.asp). I then try to run a simple query. (http://<IISServer>/<Virtual Directory Name>?sql=Select FirstName From Employees FOR XML AUTO&root=root)

The example works perfectly on the 2000 box but not on the 2003. The only difference between the two machines is that SQL Server on the 2003 machine has service pack 4 but the 2000 machine only has sp3. I can't find any articles that list problems with sp4 and I don't want to install sp4 on my 2000 box now, just in case I break something. Especially since I can't remove it once it is installed.

Has anyone run into a problem like this before or does anyone have any suggestions? I am really stuck. Thanks.

More info:

I don't think the problem has anything to do with SP4. I used the SQL Server profiler to trace any activity in the DB. When tracing the working example on the Win2000 server I can that requests are actually made by IIS. On the Win2003 box nothing shows up, so IIS isn't even sending the SQL statement to SQL Server. Any ideas on what could be preventing the request from happening?


Did you find a solution to your problem?

May be the IIS version is the underlying reason for the problem. Since IIS6 is more tight when considered with IIS5. You may need to update a few configuration settings in the IIS6 panels.