ASP Classic, SQL 2008, XML Output, and DSN vs DSN-Less Produces Chinese Letters

Posted by RoLYroLLs on Stack Overflow See other posts from Stack Overflow or by RoLYroLLs
Published on 2010-04-21T19:14:56Z Indexed on 2010/04/22 22:23 UTC
Read the original article Hit count: 445

Filed under:
|
|
|
|

I've been having a problem for the past month and can't seem to figure out what is wrong. Here's the setup and a little background.

Background:

I have a web-host who was running my website on Windows Server 2003 and SQL Server 2000. One of my webpages returned a result set from a stored procedure from the SQL server as xml. Below is the code:

Stored Procedure:

select top 10
    1 as tag
    , null as parent
    , column1 as [item!1!column1!element]
    , column2 as [item!1!column2!element]
from
    table1

for XML EXPLICIT

ASP Page: index.asp

Call OpenConn

Set cmd = Server.CreateObject("ADODB.Command")
With cmd
 .ActiveConnection = dbc
 .CommandText = "name of proc"
 .CommandType = adCmdStoredProc

 .Parameters.Append .CreateParameter("@RetVal", adInteger, adParamReturnValue, 4)
 .Parameters.Append .CreateParameter("@Level", adInteger, adParamInput, 4, Level)
End With

Set rsItems = Server.CreateObject("ADODB.Recordset")
With rsItems
 .CursorLocation = adUseClient
 .CursorType = adOpenStatic
 .LockType = adLockBatchOptimistic
 Set .Source = cmd
 .Open
 Set .ActiveConnection = Nothing
End With

If NOT rsItems.BOF AND NOT rsItems.EOF Then

 OutputXMLQueryResults rsItems,"items"

End If

Set rsItems = Nothing
Set cmd = Nothing

Call CloseConn



Sub OpenConn()
 strConn = "Provider=SQLOLEDB;Data Source=[hidden];User Id=[hidden];Password=[hidden];Initial Catalog=[hidden];"
 Set dbc = Server.CreateObject("ADODB.Connection")
 dbc.open strConn
End Sub

Sub CloseConn()
 If IsObject(dbc) Then
  If dbc.State = adStateOpen Then
   dbc.Close
  End If
  Set dbc = Nothing
 End If
End Sub

Sub OutputXMLQueryResults(RS,RootElementName)
 Response.Clear
 Response.ContentType = "text/xml"
 Response.Codepage = 65001
 Response.Charset = "utf-8"
 Response.Write ""
 Response.Write ""
 While Not RS.EOF
  Response.Write RS(0).Value
  RS.MoveNext
 WEnd
 Response.Write ""
 Response.End
End Sub

Present:

All was working great, until my host upgraded to Windows Server 2008 and SQL Server 2008. All of the sudden I was getting results like this:

From Browser:

Chinese Characters: Browser

From View Source:

Chinese Characters: View Source

However, I found that if I use a DSN connection strConn = "DSN=[my DSN Name];User Id=[hidden];Password=[hidden];Initial Catalog=[hidden];" it works perfectly fine!

My current host is not going to support DSN any longer, but that's out of scope for this issue. Someone told me to use an ADO.Stream object instead of a Recordset object, but I'm unsure how to implement that.

Question:

Has anyone run into this and found a way to fix it?

What about that ADO.Stream object, can someone help me with a sample that would fit my code?

© Stack Overflow or respective owner

Related posts about asp-classic

Related posts about sql