For XML Problem with IIS6 and W2k3

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
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
"ajsmith02" <ajsmith02@.discussions.microsoft.com> wrote in message
news:C73A1C66-4B94-489C-BA5F-6821CEB095A5@.microsoft.com...
>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
|||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.
"Michael Rys [MSFT]" wrote:

> 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
> "ajsmith02" <ajsmith02@.discussions.microsoft.com> wrote in message
> news:C73A1C66-4B94-489C-BA5F-6821CEB095A5@.microsoft.com...
>
>
|||Can you send me your private contact info (delete the online part in my
email alias)? I will get somebody from the SQLXML team to get in contact
with you to figure out where the problem lays.
Thanks
Michael
"ajsmith02" <ajsmith02@.discussions.microsoft.com> wrote in message
news:318557E2-0C62-4CDC-A964-F3EDE4A87CD4@.microsoft.com...[vbcol=seagreen]
> 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.
> "Michael Rys [MSFT]" wrote:
|||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.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"ajsmith02" <ajsmith02@.discussions.microsoft.com> wrote in message
news:318557E2-0C62-4CDC-A964-F3EDE4A87CD4@.microsoft.com...
> 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[vbcol=seagreen]
> executed then I should get a sql server error.
> Thanks for the reply.
> "Michael Rys [MSFT]" wrote:
works?[vbcol=seagreen]
pass[vbcol=seagreen]
auto[vbcol=seagreen]
no[vbcol=seagreen]
packs[vbcol=seagreen]
to[vbcol=seagreen]
otherwise[vbcol=seagreen]
output[vbcol=seagreen]
output[vbcol=seagreen]
|||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)
"Bertan ARI [MSFT]" wrote:

> 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.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "ajsmith02" <ajsmith02@.discussions.microsoft.com> wrote in message
> news:318557E2-0C62-4CDC-A964-F3EDE4A87CD4@.microsoft.com...
> string
> correctly
> and
> the
> sometimes
> the
> being
> works?
> pass
> auto
> no
> packs
> to
> otherwise
> output
> output
>
>
|||I ran iisstate against w3wp.exe and it consistantly hangs at this point
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)
"Michael Rys [MSFT]" wrote:

> Can you send me your private contact info (delete the online part in my
> email alias)? I will get somebody from the SQLXML team to get in contact
> with you to figure out where the problem lays.
> Thanks
> Michael
> "ajsmith02" <ajsmith02@.discussions.microsoft.com> wrote in message
> news:318557E2-0C62-4CDC-A964-F3EDE4A87CD4@.microsoft.com...
>
>
|||ajsmith02, did you ever find a solution to this problem? I have been struggling with the same problem for a number of months now? Would appreciate any help.
Thanks,
TuPups|||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.
"ajsmith02" wrote:

> 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
|||Nobody helped me. It turned out to be a blessing in disguise. I wound up
creating a component using .Net to pass through the "For XML" sql statements
and return the xml in for of a text stream. After I created the component I
Com Wrapper (using .NET) so that my old asp page to use it.
"ashort" wrote:
[vbcol=seagreen]
> 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.
> "ajsmith02" wrote: