Ryan’s Tech Blog

An update trigger using COLUMNS_UPDATED() was failing, and I figured out the problem:

Some simple SQL:

SELECT CAST(0×0200 as bigint), 0×0200 | 0
SELECT CAST(0×02000 as bigint), 0×02000 | 0
SELECT CAST(0×020000 as bigint), 0×020000 | 0
SELECT CAST(0×0200000 as bigint), 0×0200000 | 0
SELECT CAST(0×02000000 as bigint), 0×02000000 | 0
SELECT CAST(0×020000000 as bigint), 0×020000000 | 0
SELECT CAST(0×0200000000 as bigint), 0×0200000000 | 0
I had “IF COLUMNS_UPDATED() | 0 > 0″ in my trigger, and on large values of COLUMNS_UPDATED(), bitwise ORing with zero equals zero.  Of course, “IF COLUMNS_UPDATED() | 0 > 0″ is a retarded way to say “IF COLUMNS_UPDATED() > 0″, but whatever codepath that large number triggers wigs out there to.  Try these out:

SELECT 1 WHERE 0×0200000000 > 0
SELECT 1 WHERE CAST(0×0200000000 as bigint) > 0

So, to fix my trigger, I have “IF CAST(COLUMNS_UPDATED() as bigint) > 0″, which still seems a little retarded, but at least my trigger works.

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.