mssql ado problems

[jdonnell]

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:atabaseError)
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

 

[rpardee]

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

 

[jdonnell]

I tried that and got

c:/ruby/lib/ruby/site_ruby/1.8/DBD/ADO/ADO.rb:135:in `execute': Execute
(DBI:atabaseError)
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:ADOrovider=SQLOLEDB; Data Source = localhost;
Initial Catalog = xx; User Id = xx; Password = xxxx;')
db2 = DBI.connect('DBI:ADOrovider=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]

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