mssql ado problems

Discussion in 'Ruby' started by jdonnell, Aug 27, 2005.

  1. jdonnell

    jdonnell Guest

    I can't run two queries at the same time. The code below produces this
    error.

    c:/ruby/lib/ruby/site_ruby/1.8/DBD/ADO/ADO.rb:135:in `execute': Execute
    (DBI::DatabaseError)
    OLE error code:80004005 in Microsoft OLE DB Provider for SQL Server
    Cannot create new connection because in manual or distributed
    transactionmode.
    HRESULT error code:0x80020009
    Exception occurred. from
    c:/ruby/lib/ruby/site_ruby/1.8/dbi/dbi.rb:768:in `execute'
    from dbiTest.rb:23

    ///////////////////////////////////////////////////////////////////
    code
    ///////////////////////////////////////////////////////////////////


    sth=dbh.prepare("select * from tblProductAttributeValues where
    ProductAttributeID = '38'")
    sth.execute

    while row=sth.fetch do
    id = row[0]
    puts id

    # everything runs fine if I remove these two lines
    tmp = dbh.prepare("select top 5 * from tblDiamonds")
    tmp.execute

    end
     
    jdonnell, Aug 27, 2005
    #1
    1. Advertising

  2. jdonnell

    Guest

    Try explicitly opening up a second connection ('dbh2', say) and using
    that for your second query. My *guess* is that your first connection
    is too busy slurping rows for your first query to handle a second
    query.

    HTH,

    -Roy
     
    , Aug 27, 2005
    #2
    1. Advertising

  3. jdonnell

    jdonnell Guest

    I tried that and got

    c:/ruby/lib/ruby/site_ruby/1.8/DBD/ADO/ADO.rb:135:in `execute': Execute
    (DBI::DatabaseError)
    OLE error code:80004005 in Microsoft OLE DB Provider for SQL Server

    Cannot create new connection because in manual or distributed
    transaction mode.

    ///////////////////////////////////////////////////////////////////
    code
    ///////////////////////////////////////////////////////////////////

    require 'dbi'

    # connect to a datbase
    dbh = DBI.connect('DBI:ADO:provider=SQLOLEDB; Data Source = localhost;
    Initial Catalog = xx; User Id = xx; Password = xxxx;')
    db2 = DBI.connect('DBI:ADO:provider=SQLOLEDB; Data Source = localhost;
    Initial Catalog = xx; User Id = xx; Password = xxxx;')
    sth=dbh.prepare("select * from tblProductAttributeValues where
    ProductAttributeID = '38'")
    sth.execute

    while row=sth.fetch do
    id = row[0]
    puts id

    tmp = dbh2.prepare("select top 5 * from tblDiamonds")
    tmp.execute
    end

    dbh.disconnect
    gets
     
    jdonnell, Aug 29, 2005
    #3
  4. jdonnell

    Roy Guest

    Hmmm... I wonder if the connections are getting pooled under the
    covers maybe? Any diff if you have dbh2 connect as a different user?
    I believe that more subtle changes will throw off conn pooling too, but
    I'd start out w/the stark case of a second user.

    If that does work, then it's probably worth looking into means for
    disabling connection pooling more directly.

    I (I think) just solved a similar problem (same err msg anyway) by
    throwing a gratuitous

    dbh.commit

    in after a call to DBI::StatementHandle.execute, even tho the SQL in
    question was just a simple SELECT. But I didn't need to execute a
    second statement in the middle of a Fetch loop like you are.

    HTH,

    -Roy

    jdonnell wrote:
    > I tried that and got
    >
    > c:/ruby/lib/ruby/site_ruby/1.8/DBD/ADO/ADO.rb:135:in `execute': Execute
    > (DBI::DatabaseError)
    > OLE error code:80004005 in Microsoft OLE DB Provider for SQL Server
    >
    > Cannot create new connection because in manual or distributed
    > transaction mode.
    >
    > ///////////////////////////////////////////////////////////////////
    > code
    > ///////////////////////////////////////////////////////////////////
    >
    > require 'dbi'
    >
    > # connect to a datbase
    > dbh = DBI.connect('DBI:ADO:provider=SQLOLEDB; Data Source = localhost;
    > Initial Catalog = xx; User Id = xx; Password = xxxx;')
    > db2 = DBI.connect('DBI:ADO:provider=SQLOLEDB; Data Source = localhost;
    > Initial Catalog = xx; User Id = xx; Password = xxxx;')
    > sth=dbh.prepare("select * from tblProductAttributeValues where
    > ProductAttributeID = '38'")
    > sth.execute
    >
    > while row=sth.fetch do
    > id = row[0]
    > puts id
    >
    > tmp = dbh2.prepare("select top 5 * from tblDiamonds")
    > tmp.execute
    > end
    >
    > dbh.disconnect
    > gets
     
    Roy, Sep 19, 2005
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    16
    Views:
    4,295
    gregarican
    Jan 19, 2006
  2. Replies:
    2
    Views:
    695
    Steve Holden
    Jan 18, 2006
  3. Replies:
    1
    Views:
    192
  4. David De vits

    mssql image field: ado returns an array

    David De vits, Feb 5, 2008, in forum: Ruby
    Replies:
    3
    Views:
    131
    Simon Vetterli
    Feb 20, 2008
  5. doug
    Replies:
    1
    Views:
    140
    Julia De Silva
    Jun 7, 2005
Loading...

Share This Page