From: Roy <rpardee@...>
Date: 2005-09-20T07:06:37+09:00
Subject: Re: mssql ado problems

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