Any result from the MDAC team?  I am fighting the same issue, and would be
very interested in a solution or workaround.

Thanks for the explanation. I am checking with the MDAC team to see if they
are aware of the issue.

Thanks
Michael

I have started experiencing the same problem when my company switched
to the Windows 2003 servers.  Since the switch, our asp web pages that
contain the adodb.stream objects will randomly lockup until the
application pool is recycled in IIS.  Then everything will run smoothly
for a few days.  All ASP pages that don't contain the adodb.stream
object will continue to work as normal, when the lockup occurs.  No
errors are generated in the logs and the web server and database server
doesn't show any signs of heavy memory or CPU usage.  You can even
run the "FOR XML AUTO" stored procedures in query analyzer without
any errors during this time.  To try and prevent the problem we setup
the application pools to be recycled nightly, but that doesn't appear
to make any difference.  We have Microsoft SQL Server on a different
machine then our web server, but I doubt that makes a difference.  The
offending ASP pages were working perfectly fine without any issues when
the server was still Windows 2000 Server.

Here is the basics of the offending ASP pages:

<%@ Language=VBScript %>
<%Option Explicit%>
<%
Dim objCommand,objXML,objStream,objRoot

    Set objCommand = Server.CreateObject("ADODB.Command")
    Set objXML = Server.CreateObject("MSXML2.DomDocument")
    Set objStream = Server.CreateObject("ADODB.Stream")

    objStream.Open
        With objCommand
            .ActiveConnection = "Provider=SQLOLEDB; Data Source=ExampleServer;
Network Library=DBMSSOCN; Initial Catalog=ExampleDB; User Id=Example;
Password=Example"
            .CommandType = adCmdStoredProc
            .CommandText = "SP_Example"
            .Properties("Output Stream") = objStream
            .Execute ,, adExecuteStream
        End With

    objXML.loadXML("<root>" & objStream.ReadText & "</root>")
    Set objRoot = objXML.documentElement
%>

I have not included the rest of the code that displays the returned
data because the page does not appear to get past the Execute
statement.

The stored procedure is basically:

CREATE PROCEDURE SP_Example
AS
SELECT ExampleID, ExampleName from ExampleTable
FOR XML AUTO

GO

Any suggestions, beyond recoding every web page, would be greatly
appreciated.

Thanks.
Paul

Hi,

Could anyone reproduce the problem with a generic ASP page against e.g.
Northwind database?  If so, please send me the code if possible.  I have the
following ASP page and run it for days on Windows Server 2003 and SQLXML3
SP3, but didn't see it hanging using a stress app.  

Also, Windows Server 2003 SP1 can be downloaded, so if someone could give it
a try, it would be great.

<!--#include file="common.inc"-->
<% Response.ContentType = "text/xml" %>
<object id="conn" progid="ADODB.Connection" runat="Server"></object>
<%
Dim strSQL, getSQLXML, strSQLXML, objCmd
dim outStrm

set outStrm = Server.CreateObject("ADODB.Stream")     'Create the output
stream
outStrm.Open

strSQL = "<sql:query>select * from orders for xml auto</sql:query>"
strSQLXML = "<?xml version=""1.0"" ?><root
xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" & strSQL & "</root>"

conn.Open strConnNew

Set objCmd = Server.CreateObject("ADODB.Command")

objCmd.ActiveConnection = conn
objCmd.CommandText = strSQLXML
objCmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
objCmd.Properties("Output Stream").Value = outStrm

objCmd.Execute , , 1024
outStrm.Position = 0
outStrm.Charset = "utf-8"
getSQLXML = outStrm.ReadText(-1)
outStrm.Close
Response.Write("<root/>")
%>

thx,
-kuen

Folks,
Has ANYONE solved this problem?  I am getting "Catastrophic failure
messages and Err.Number = -2147418113 from my ASP pages, consistently

Thanks in advance...

- Tom

-
TomKellehe
-----------------------------------------------------------------------

Did anyone ever resolve this?  I am having the exact same issue...returning
results from a FOR XML procedure to an ado stream object stops working every
several days.

I have this function that worked like a charm under IIS5 and W2K.  You pass a
sql string that has for xml auto or a stored produre that has for xml auto in
it.  Under IIS6 and W2K3 it stops working after a couple of days with no
rhyme or reason.  No error log either.  We applied all the service packs
including sqlxml sp3.  What is wrong? Thanks.

Here is the code:
function getSQLXML(byval sqlString)
    dim adoConn
    dim adoCmd
    dim adoStreamQuery
    set adoConn = vbsqlconnection 'located in sharedfunctions.asp
    adoConn.CommandTimeout = 300
   
    set adoStreamQuery = Server.CreateObject("ADODB.Stream")
    set adoCmd = Server.CreateObject("ADODB.Command")'
    adoCmd.ActiveConnection = adoConn
    adoCmd.CommandTimeout = 300
    adoConn.CursorLocation =  adUseClient

    dim sQuery  
    sQuery = "<recordset xmlns:sql='urn:schemas-microsoft-com:xml-sql'>"
    sQuery = sQuery + "<sql:query>"+sqlString+"</sql:query>"
    sQuery = sQuery + "</recordset>"
               
    adoStreamQuery.Open    'Open the command stream so it may be written to
    adoStreamQuery.WriteText sQuery, adWriteChar   'Set the input command
stream's text with the query string
    adoStreamQuery.Position = 0   'Reset the position in the stream, otherwise
it will be at EOS

    adoCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"   'Set the
dialect for the command stream to be a SQL query.
    adoCmd.CommandStream = adoStreamQuery     'Set the command object's command
to the input stream set above

    dim outStrm
    set outStrm = Server.CreateObject("ADODB.Stream")     'Create the output
stream
    outStrm.Open

    adoCmd.Properties("Output Stream").Value = outStrm  'Set command's output
stream to the output stream just opened    
    adoCmd.Execute , , adExecuteStream
    'Response.Write(outStrm.ReadText)
    adoCmd.ActiveConnection = nothing
    adoConn.Close
    set adoConn = nothing
    getSQLXML = outStrm.ReadText
end function

P.S. Goorbeeman in the group microsoft.public.sqlserver.server has the same
problem