SQL Server 2005 Express Service Pack 2 w/Advanced Services
Windows 2003 R2
I recently migrated a database from MSDE 2000 to MSSQL 2005 that is used for a tourist website that can be displayed in one of three languages. A stored procedure is executed in ASP with a number that represents the language to use
Dim xml: xml = "<?xml version=""1.0"" encoding=""UTF-8""?>" response.write "<root>" set conn = server.createobject("ADODB.connection") conn.open application("bd") sqlTrad="EXEC psu_sitFicheTraduction @idLangue=1" set rsTrad = Server.CreateObject("ADODB.Recordset") rsTrad.Open sqlTrad, conn, 1,1 do while not rsTrad.eof texte = rsTrad(0) response.write texte rsTrad.movenext loop rsTrad.close
The stored procedure EXEC psu_sitFicheTraduction runs the following SELECT query:
SELECT 1 AS Tag, NULL AS Parent, t.PK_txt AS [dicItem!1!ID], t.libelle_long_FR AS [dicItem!1!value], @idLangue as [dicItem!1!Langue] FROM txt_traduction t FOR XML EXPLICIT
For some reason, on the new server, the XML is displayed incorrectly:
This page contains the following errors:
error on line 1 at column 51: PCDATA invalid Char value 1 Below is a rendering of the page up to the first error.
This is the source of the page:
When I run the stored procedure in SSMS, I don't get any errors although I have noticed that in Query Analyzer on the MSDE 2000 server I get 23 rows, where as in SQL Server 2005 SSMS I only get one big row.
This is the first time I've seen FOR XML EXPLICIT used so I don't have any experience with it.
- Why is MSDE sending back 23 rows and SSMS only 1 big one
- Any thing I need to configure in IIS to correctly display XML output coming from SQL Server ?
Any other suggestions or assistance would be appreciated.