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:
ɄIDՄvalueلLangue݄dicItemЁĂłP牵牥̂łༀ䌀慨扭敲搠栧瓴獥̂ł䈃ЁĂ奂ȀဂӤ䈃摲❩✬敍捲敲楤Ⱗ䨧略楤Ⱗ嘧湥牤摥❩✬慓敭楤崧̂łɲ䈃
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.
