Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

  1. Why is MSDE sending back 23 rows and SSMS only 1 big one
  2. 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.

share|improve this question
    
That query should return one row. Not sure what MSDE is doing. Does it return well-formed XML? I think there may be an issue with character encoding here, too. –  Jon Seigel Jan 9 '13 at 19:17
    
In SSMS, the XML looks well formed, its in IIS that I get all those Asian characters above. –  Craig Efrein Jan 10 '13 at 9:54
    
Okay, the issue is a character encoding issue then. XML is always 2 bytes per character, but if the application is expecting 1 byte per character, you get garbage output like that. –  Jon Seigel Jan 10 '13 at 14:09
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.