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.
7 Comments