UPDATE:
the site has been stable for a week now using the SQLXMLOLEDB provider, so
I'm assuming that the problem is indeed in the SQLOLEDB files themselves.  
I'm loading the MDAC 2.8 hotfixes now (from
http://support.microsoft.com/kb/838976/), after which I will switch back to
the original SQLOLEDB connection string.  I'll post a followup with my
results.

//bob

Thanks for the tip, Paul.  It looks like the most promising lead I've seen.

As a possible workaround, I switched to the SQLXMLOLEDB provider, since this
is a straight XML request.  Although it's too soon to tell, the page has been
stable for 3 days now, which is the longest it's gone so far without a
freeze-up.  I'm going to let it go a full week, and then I'll install the
hotfix from Paul's KB link and switch back to the SQLOLEDB provider to test.  
I'll report my results back here.

//bob

Well, my company called into Microsoft yesterday and they gave us a
couple of solutions.  We haven't implemented any of them yet, so take
them with a grain of salt.  The problem is that the sqlxmlx.dll is
unloading unexpectedly, which causes the web pages to lockup.

There at three possible solutions.

1. Install the hotfix associated to this KB:
http://support.microsoft.com/?kbid=838976

2. Install the MDAC 2.8 hotfixes associated to this KB:
http://support.microsoft.com/?kbid=839801 (includes the above hotfix)

3. Install W2K3 Service Pack 1 (includes both of the above)

I think we are going to try solution one first and go from there.
Again, we haven't tried them yet and since the problem doesn't happen
daily, when we do implement them we won't know for a while if it is
successful.  Hopefully this helps.

Paul

The code I posted is the complete content of the "links.asp" problem page.  
This page is called via an ASP Server.Execute command inside another page.  
This "parent" page does have 2 database routines of it's own - both use the
ADODB.Connection "Execute" method to run stored procedures that return
recordsets into variables (no recordset objects explicitly created).  As far
as I know, the default "Execute" behavior is synchronous, yes?  I certainly
am not specifying the asynchronous option.  After the recordsets are
returned, I explicitly close and "nothing" the recordsets, then close and
"nothing" the connection object. This code is all supposed to run and
complete before the "links.asp" page gets executed.

Does any of this info raise a red flag?

//bob

The stack you provided shows a problem with releasing a pointer to a rowset.
However the code fragment you posted does not have any rowsets defined,
unless I'm missing something.
You should see where you might have recordsets created with asynchronous
execution.

Below I have copied my post from the data.ado group - it contains my ASP code
and the IISState log for the problem thread.  I can provide module versions,
if you wouldn't mind telling me which dll's to examine.  I did just install
the MDAC 2.8 upgrade, but the behavior was the same before as after.  I'm not
sure how to provide a stack trace, but I'll see if I can Google something on
it.  I'd be happy to provide anything I can  to help solve this issue.

//bob

--snip--
Like many others, I am struggling with a problem in Win2K3 and IIS6 using an
ADO Stream object and an SQL FOR XML stored procedure.  Let me preface by
saying that the ASP code has worked flawlessly on Win2K and IIS 5 for over a
year.  Now on Win2K3, the page will work for a while, then it becomes
unresponsive.  It returns no error, it just hangs the connection so that no
other site page will respond until the browser is closed and re-opened.  
Sometimes recycling the app pool will clear it up temporarily, sometimes I
have to restart IIS.  Page will work fine for a few hours or even days, then
it stops responding.  CPU and memory utilization seem normal in TaskMan.

First, here's the ASP code:

<%
 dim cn
 set cn = server.CreateObject("ADODB.Connection")
 cn.open MM_iiWeb_STRING
 dim result
 result =  getSQLXML(cn, "exec proc_getLinkCatXML", "linkpage.xsl", ".")
 response.Write result
 if cn.state=1 then cn.close()
 set cn = nothing   

%>

<%
function getSQLXML(byref cn, byval sql, byval xslfile, byval basepath)
 dim cmd
 dim objOutStream
 Set objOutStream = Server.CreateObject("ADODB.Stream")
 objOutStream.open
 Set cmd = Server.CreateObject("ADODB.Command")   
 cmd.ActiveConnection = cn
 cmd.CommandText = sql
 cmd.Properties("Base Path").Value = Server.MapPath(basepath)   
 cmd.Properties("XML Root") = "root"
 cmd.Properties("XSL") = xslfile
 cmd.Properties("Output Stream") = objOutStream
 cmd.Execute , , adExecuteStream   

 getSQLXML =  objOutStream.ReadText
 objOutStream.Close
 set objOutStream = nothing
 set cmd = nothing   
end function
%>

When it hangs, here is IISState log info for the thread:

Thread ID: 15
System Thread ID: bc0
Kernel Time: 0:0:0.109
User Time: 0:0:0.968
Thread Type: ASP
Executing Page: C:\INETPUB\WWWROOT\IIWEB\TEMPLATES\LINKS.ASP

# ChildEBP RetAddr  
00 025de3d8 77f4262b SharedUserData!SystemCallStub+0x4
01 025de3dc 77e418ea ntdll!NtDelayExecution+0xc
02 025de444 77e416ee kernel32!SleepEx+0x68
03 025de450 0439c4f9 kernel32!Sleep+0xb
04 025de464 04382a20 msado15!CQuery::Cancel+0x50
05 025de49c 0437d84d msado15!CCommand::Cancel+0x58
06 025de4ac 0437516f msado15!CCommand::Term+0xd
07 025de4c8 04373951 msado15!CStdSymbiontObject::InternalRelease+0x6c
08 025de4d8 7710736a msado15!ATL::CComObject<CRecordset>::Release+0x11
09 025de4e8 7346384b OLEAUT32!VariantClear+0xad
0a 025de4fc 734641cf vbscript!VAR::Clear+0xab
0b 025de50c 7346416a vbscript!CScriptRuntime::Cleanup+0x59
0c 025de84c 73465184 vbscript!CScriptRuntime::Run+0x2ccc
0d 025de504 80020102 vbscript!CScriptRuntime::Run+0x99
WARNING: Frame IP not in any known module. Following frames may be wrong.
0e 025de504 80020102 0x80020102
0f 00000000 00000000 0x80020102

I have seen the same problem posted several times in various newsgroups and
message boards for the past 8 months or so, but I have seen no explaination,
solution, or workaround offered.  I would appreciate any insights.

//bob

--end snip--

MDAC team hasn't seen the problem you describe in our internal tests.
Does this page lockup mean that the thread executing the script hangs?
If so is it possbile to get the stack trace and the version information for
the modules involved?

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