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:
From 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?