OK.  Will do.  I only posted in case some more information had been found on
the problem since then, but this is still MUCH more information than we've
been able to find elsewhere.  I've also verified that our IT support staff
has not installed SP1 on our Windows 2003 server, so the suggestions below
might just work out.

Thanks for your help.

Keith

Hi Keith,
Here are a few workarounds that you could try. These were the solutions
given when we saw this issue last time.We were not able to repro it last
time.

You could try this and see if any of these solutions work for you. (These
are posts from people who had a similar problem)

1.

I think we are going to try solution one first and go from there.

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

"Bob Hobnob" wrote:

HTH,

Chandra

------------------------------------------------------

Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

Hi Keith,
I have asked one of my team members to look at this. It seems we have seen
similar issue once before.
It would help if you can give me a SqlQuery to execute and sample data for
this scenario.

thanks
Chandra

Hi,

Our code, with the exception of a few On Error Statements, is exactly the
same as the code block listed by ajsmith02 below.  Is that enough to go on?

Thanks

Keith

Can you post  a repro of this scenario or the one that you have. I can ask
the stress team here to run and analyse this one.

Thanks
Chandra

Hi All,

I am having the EXACT same problem that all of you have so eloquently
described.  Was there any luck with either the patches or the use of
SQLXMLOLEDB instead of SQLOLEDB?

Thanks much.

Keith

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