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