“manual or distributed transaction mode”, ruby, rails, mssql, ado
Spent too much damn time debugging the database layer in rails again today. This was the error:
OLE error code:80004005 in Microsoft OLE DB Provider for SQL Server
Cannot create new connection because in manual or distributed transaction mode.
This was getting thrown after an insert, and the problem was very non-obvious. I found one potential solution on Occasionally Useful Software’s post about Ruby and SQL Server, but that seemed a little heavy-handed.
Somewhere in the bowels of ActiveRecord, dbi.rb, ADO.rb, and the SQLNCLI driver, something expected one result-set per SQL command. My insert was firing a database trigger, and so the ruby stack saw two result-sets, one saying “1 row affected” for the original insert, one saying “0 rows affected” for the trigger. I’m still a little confused, as I thought those “rows affected” messages were separate from proper result-sets (the results from a SELECT, for example), but apparently those little buggers count enough.
Adding a “SET NOCOUNT ON” to the top of the trigger fixed it.
joby Sebastian said,
June 21, 2008 @ 9:37 am
This is a very good fix when we are doing more query update in one migration
Simon Francesco said,
August 5, 2008 @ 12:40 am
Smokin’.
I was running a newly introduced Stored Procedure which ran fine except the next resultset I tried to open threw the “Cannot create new connection because in manual or distributed transaction mode” error.
This little fix saved me a lot of time. Cheers