I am seeing the same thing.  The queries continue to execute just fine
through Query Analyzer.  The ASP pages in IIS run fine for days with no
apparent problem, but after a while they begin to hang. Each time, an
IISRESET fixes the problem, but it always returns.  Looking at the
resources in the task manager, you'd never know anything was wrong;
memory and CPU usage are so low they barely register.  No errors seem
to be reported anywhere.  IIS itself even continues to serve pages
without any noticable slowdown.  The only pages that seem to be
affected are the ones using ADODB commands and streams to read XML
query results.

I tried running a simple for xml query on W2K3 IIS6 for almost a week and
haven't seen any AV/hangs or exception under a debugger.  Could you send me a
sample of your xml query that caused the problem?  Did you see any unusual
behavior in sqlserver process when it happened?

thanks,
-kuen

I ran iisstate against w3wp.exe.  When the web app hangs here is the
consistant error
ModLoad: 74540000 745d2000   C:\WINDOWS\system32\mlang.dll
(e88.5d0): Access violation - code c0000005 (first chance)
(e88.5d0): C++ EH exception - code e06d7363 (first chance)

You can send your case directly to me: bertan at gmail dot com. Please,
iclude your vb script and query, your schema/template if there is any.

In the mean time, I don't see that you are using IIS anywhere here. You are
simply using ADO. IIS shouldn't be the issue here.

I am afraid your problem lies somewhere in your machine/system
configurations. The only issue I know for SqlXml3 on Win2003 is that you
have to install Soap toolkit seperately.

Thanks.

To specify the problem.  This chunk of code has been working for about 3
years on IIS5 and W2K.  The sql that gets executed is passed in as a string
variable.  To answer your question the for xml queries always runs correctly
in sql server.  In the IIS6 and W2k3 combo this code runs fine for days and
then all of a sudden it bombs without an error message.  I have isolated the
place in the asp code snippet below on this line:

adoCmd.Execute , , adExecuteStream

My next step is to recycle the application pool and for the most part that
gets things going again.  Sometimes I have to restart IIS and still sometimes
I have to reboot the server all together.  If something where wrong with the
code then it should never work.  If something were wrong with the sql being
executed then I should get a sql server error.

Thanks for the reply.

Can you run the FOR XML query directly on the database?

Have you tried a different template/query to see if the connection works?

Best regards
Michael

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