Data access in XML from Sql-server200 using asp

From: Vaibhav (Vaibhav_at_discussions.microsoft.com)
Date: 06/17/04

  • Next message: ben h: "stored proc RETURN value in ASP" 
    Date: Wed, 16 Jun 2004 23:21:01 -0700

    Dear all,

    i wornder is some one help me.
      
    i want to access the the data from ms-sql-server2000 to xml file using asp so my
    code is as

    <%@ Language=VBScript %>

    <!--#include file="adovbs.inc" -->
    <%

        Dim adoCmd 'As ADODB.Command
        Dim adoConn ' As ADODB.Connection
        Dim adoStreamQuery ' As ADODB.Stream
        Dim outStrm ' As ADODB.Stream
        Dim txtResults ' String for results
        dim sConn ' String for connection
        dim CmdStream ' as ADODB.Stream
        
        Const adExecuteStream = 0
        
        sConn = "Provider=SQLOLEDB;Data Source=TRIDT028;Initial Catalog=Northwind;uid=sa;pwd=trident"
        'sConn = "Provider=SQLXMLOLEDB.3.0;Data Provider=SQLOLEDB;Data Source=TRIDT028;Initial Catalog=Northwind;uid=sa;pwd=trident"

        Set adoConn = CreateObject("ADODB.Connection")
        Set adoStreamQuery = CreateObject("ADODB.Stream")
        
        adoConn.ConnectionString = sConn
        adoConn.Open
        
        Set adoCmd = CreateObject("ADODB.Command")
        set adoCmd.ActiveConnection = adoConn
        
        adoConn.CursorLocation = adUseClient
        
        Set adoCmd.ActiveConnection = adoConn
          sQuery = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>"
          sQuery = sQuery & "<sql:query>SELECT * FROM PRODUCTS FOR XML AUTO</sql:query>"
          sQuery = sQuery & "</ROOT>"
                
        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
        
        Set adoCmd.CommandStream = adoStreamQuery ' Set the command object's command to the input stream set above
        adoCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}" ' Set the dialect for the command stream to be a SQL query.
        Set outStrm = CreateObject("ADODB.Stream") ' Create the output stream
        outStrm.Open
        'adoCmd.Properties("Output Stream") = response ' Set command's output stream to the output stream just opened
        adoCmd.Properties("Output Stream") = outStrm
        adoCmd.Execute , , adExecuteStream ' Execute the command, thus filling up the output stream.
    %>

    but it is giving the error

    ADODB.Command (0x800A0CB3)
    Object or provider is not capable of performing requested operation.

    if i will use the SQLXMLOLEDB as provider and OLEDB as data provider
    then it is giving the error

    SQLXMLOLEDB Provider command object can only execute to a stream.

    so please suggest me the solution.
    thanking u

    Prashant

  • Next message: ben h: "stored proc RETURN value in ASP"

    Relevant Pages

    • Accessing SQL-Server data in XML
      ... Dim adoStreamQuery ' As ADODB.Stream ... Dim txtResults ' String for results ... Set adoCmd.CommandStream = adoStreamQuery ' Set the command object's command to the input stream set above ... Object or provider is not capable of performing requested operation. ...
      (microsoft.public.inetserver.asp.db)
    • RE: Run a Stored Procedure from Excel
      ... You also want to find out if you have a rsDW variable defined. ... Your code had a semicolon between the 2nd command and the ... Dim cnnDW As ADODB.Connection ... Set cnnDW = New ADODB.Connection ...
      (microsoft.public.excel.programming)
    • RE: Run a Stored Procedure from Excel
      ... "You also want to find out if you have a rsDW variable defined. ... Your code had a semicolon between the 2nd command and the ... Dim cnnDW As ADODB.Connection ... Set cnnDW = New ADODB.Connection ...
      (microsoft.public.excel.programming)
    • Re: Need WMI script
      ... WinNT provider reveals the "primary" group, ... I also assume that strGroupName ... Dim adoCommand, adoConnection, strBase, strFilter, strAttributes, ... However, if you use the cscript host, ...
      (microsoft.public.windows.server.scripting)
    • RE: Open Access 2003 or 2007 from Excel
      ... 'EXAMPLE OF ACCESS COMMAND TO CONTROL FORMS ... 'Dim dbMain As ADODB.Connection ... 'Dim dbMain As ADODB.Connection 'compile error: ... new computers that now have VISTA as the OS. ...
      (microsoft.public.excel.programming)
    Loading