Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Location
    Sussex
    Posts
    5

    Unanswered: ADODB.Command error '800a0cb3'

    I have posted various questions on the microsoft ng trying to identify the cause of this error.

    ADODB.Command error '800a0cb3'
    Object or provider is not capable of performing requested operation.

    I have MDAC 2.8 installed locally on my machine.

    Via IIS I created a virtual directory via IIS that points to my ASP files on c:

    Via the SQL Server IIS for XML configuration utility I created a virtual directory with a different names that points to the same directory as that created via IIS.

    The SQL database is on a different machine and I connect via the OLEDB DSNless connection string.

    I used a ADODB.Stream to transform the XML against the XSL but I couldnt get it to work. To simplify things and work towards a solution I inserted the code into my ASP from the MS KB article Q272266 (see below). I amended the ms code to change the connection code and call a stored procedure that exists on the database. The ms code gives the same error as my original code.

    I tried changing the CursorLocation to server and client but the results were the same.

    I put a SQL trace on the DB to determine if the stored procedure gets ran, it does not.

    If I run the following in the URL it works:

    If I run http://localhost/p2/?sql=SELECT+*+FR...StatusDesc.xsl it works.
    If I run the xml template it works: http://localhost/p2/xml/test.xml

    The two lines above run. My IIS server uses a virtual directory called dev, so when I run the ASP I type http://localhost/DEV/secure/aframes.asp the IIS virtual directory creted by sql server is called p2 but has the same source code directory.

    Here is the MS code amended as described above that does not work.

    sQuery = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:query>Select StatusDesc from tblStatus for XML Auto</sql:query></ROOT>"
    '*************************************************

    Dim txtResults ' String for results
    dim CmdStream ' as ADODB.Stream


    sConn = "Provider=SQLOLEDB;Data Source=[name of sql server];UId=sa; Pwd=xxxxx; Initial Catalog=[DB Name]"

    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

    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.Execute , , adExecuteStream ' Execute the command, thus filling up the output stream.

    Response.End

  2. #2
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: ADODB.Command error '800a0cb3'

    try this type of connection string

    driver={SQL Server};server=ServerName;uid=sa;pwd=xxxx;Database =DBName

  3. #3
    Join Date
    Feb 2004
    Location
    Sussex
    Posts
    5

    Unhappy

    Thanks for your response.

    I think this is progress but it seems to have broken the command object and it doesn't do the execute, it fails on the line:

    adoCmd.Properties("Output Stream") = response

    and gives an error of:

    Item cannot be found in the collection corresponding to the requested name or ordinal.

  4. #4
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    the maybe try this provider woth your first connectionstring

    Provider=SQLOLEDB.1;

  5. #5
    Join Date
    Feb 2004
    Location
    Sussex
    Posts
    5
    No, sorry, this gives the error;

    Object or provider is not capable of performing requested operation.

    On the line with the execute statement.

    What I am thinking of doing is to install sql server locally and have the db on my c: and try again, perhaps it is something to do with win 2k permissions and accessing the sql server across the network. Or perhaps its cause we use host headers on the web server?

    Code now:


    sQuery = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:query>Select StatusDesc from tblcStatusDesc for XML Auto</sql:query></ROOT>"

    'sql = "<dataroot xmlns:sql='urn:schemas-microsoft-com:xml-sql'>"
    'sql = sql & "<sql:header><sqlaram name='number'>" & memberNo & "</sqlaram></sql:header>" & sqlQuery & "</dataroot>"

    'dim sQuery
    'sQuery = sql
    dim sXSLfile
    sXSLfile = strXSL

    'sConn = "Provider=SQLOLEDB;Data Source=LON09WEB;UId=sa; Pwd=develop; Initial Catalog=NMRA_INETSOURCE"

    Set adoConn = CreateObject("ADODB.Connection")
    Set adoStreamQuery = CreateObject("ADODB.Stream")
    set objError = CreateObject("ADODB.Error")
    Set adoCmd = CreateObject("ADODB.Command")

    sConn = "Provider=SQLOLEDB.1;Data Source=LON09WEB;UId=sa; Pwd=develop; Initial Catalog=NMRA_INETSOURCE"
    'sConn="Provider=SQLOLEDB.1;driver={SQL Server};server=LON09WEB;uid=sa;pwd=develop;Databas e=NMRA_INETSOURCE"

    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

    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.Execute , , adExecuteStream ' Execute the command, thus filling up the output stream.

    Set adoConn = NOTHING
    Set adoStreamQuery = NOTHING
    set objError = NOTHING
    Set outStrm = NOTHING

  6. #6
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    Why are you using the Stream property ???

  7. #7
    Join Date
    Feb 2004
    Location
    Sussex
    Posts
    5
    I'm not really too sure. I've been copying code samples from different places and ended up with the stream property in the code. My aim is to transform the xml against xsl. I've managed to get the xml bit working all I need now is to add the xsl file name.

    Dim oCmd, sSQL

    Set adoConn = CreateObject("ADODB.Connection")

    sConn = "Provider=[Server];Data Source=xxx;UId=xxx; Pwd=xxx; Initial Catalog=xxx"

    Set oCmd = Server.CreateObject("ADODB.Command")
    oCmd.ActiveConnection = sConn

    oCmd.CommandText = sQuery
    oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
    oCmd.Properties("base") =
    oCmd.Properties("xsl") = "tblcStatusDesc.xsl"
    oCmd.Properties("Output Stream") = Response
    oCmd.Execute , , 1024

  8. #8
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    I could give you VB code to use your db

    > open/close connections
    > open/commit/rollback transactions
    > select data
    > execute command queries


    I going home soon


    I'll be back only on monday (loooong WE comming up !)
    if this interestes you, mail me monday

  9. #9
    Join Date
    Feb 2004
    Location
    Sussex
    Posts
    5
    Thanks for your help, have a great weekend.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •