I wrote the code on my development machine WinXPsp3, which has apache standalone 2.2.21. The query worked fine.
I then copied the php over my production machine WinXPsp3, which had apache 2.2 windows installation with php 5.3.5 windows installation, and is the SQL Server machine, but the query crashed.
I uninstalled apache and php on the production machine and copied my stand-alone version over from the development machine, but when I started apache, I got an error that MSCPV100.dll was missing. I installed MS C++ Redist, and that problem went away.
The query is as follows:
SELECT TOP (50)
[ID],
(SELECT Case
WHEN [Location] is null then 'Unknown'
WHEN [Location]='' THEN 'Unknown'
ELSE UPPER([Location]) END
+ ', ' as [text()] FROM dbo.[OtherTable]
WHERE [OtherTable].[Key]=[Table].[ID] FOR XML path('')) AS Locations
FROM Table
(any mis-formatting here is not reflective of my actual query, as it works on other apache installations)
The query is choking on the lower-case "AS" before [text()] with "Incorrect Syntax near the Keyword 'as'".
The query was originally in uppercase, and had many more fields, so I used lower case until I found out which "AS" the failure description was talking about.
I tried replacing the "[]" around text() with "''", but that didn't help.
The facts I know to be true:
The apache installations are identical.
The php installations are identical.
The query works fine when hosted through the identical installation
of apache on a different machine.Other queries are working fine on the production machine after the
apache/php upgrade.
This information leads me to believe that there's something awry with the production machine, and not apache, sql server, php, or the query. Any ideas as to what that might be?
A couple of things I didn't mention:
The PHP connection type is odbc.
The SQL Server ODBC drivers on both machines are 2000.85.1132.0, but the production machine also has SQL Server Native Client 10.0 version 2007.100.2531.00.
