Re: For XML Problem with IIS6 and W2k3

From: Michael Rys [MSFT] (mrys_at_online.microsoft.com)
Date: 10/07/04 

Date: Thu, 7 Oct 2004 00:36:47 -0700

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

Relevant Pages

  • Re: WSS Web Services Help
    ... use the Lists Web service to exchange XML data directly with the SharePoint ... Dim lws As New clsws_Lists ' Requires Web reference to to SharePoint ... Dim xn As IXMLDOMNodeList ' Requires reference to Microsoft XML ... Dim query As IXMLDOMNodeList ...
    (microsoft.public.sharepoint.windowsservices)
  • Re: For XML Problem with IIS6 and W2k3
    ... iclude your vb script and query, your schema/template if there is any. ... > in sql server. ... >>> dim adoConn ... >>> dialect for the command stream to be a SQL query. ...
    (microsoft.public.sqlserver.xml)
  • Re: Xml error: Namespace Manager or Xslt context needed
    ... "XML newbie: Urgent pls help!" ... ' Instantiate an XmlDocument object to avoid NullReference ... Dim xmlDoc As XmlDocument ... Dim strm As Stream = HttpWResponse.GetResponseStream ...
    (microsoft.public.dotnet.xml)
  • Re: Viewing xml returned from stored procedures
    ... the XML document first? ... > Dim conn ... > 'Execute the command returning a stream ...
    (microsoft.public.sqlserver.xml)
  • Re: Query results to file
    ... Dim Mydataset As DataSet ... I ALREADY have XML which is output from the stored procedure in a column. ... So I guess I need some kind of stream object from this column that ... I want to execute this query and write the results to a file. ...
    (microsoft.public.dotnet.languages.vb)