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?
