12 March 2007

NHibernate and triggers

Recently I was asked from a college of mine, about the following exception in application based on SQL Server 2005 and NHibernate :
[NHibernate.AdoNet.TooManyRowsAffectedException] {"Unexpected row count: 2; expected: 1"} NHibernate.AdoNet.TooManyRowsAffectedException

After a quick investigation the problem was obvious - the exception raises when a row from a table is updated and the row has fired a trigger which executes an update statement.
I have faced with this problem since early beta of NHibernate (0.8.4 as far as I can remember). Till now the only solution I have found is to use SET NOCOUNT to stop the message that shows the number of rows affected by a Transact-SQL statement from being returned as part of the results.
Just place SET NOCOUNT ON before the UPDATE statement inside the trigger and then after the UPDATE statement place SET NOCOUNT OFF. This will solve the problem.

13 comments:

JRH said...

Thanks, Stephan... You've saved my day today!

Tim said...

Mine too.

Stephan said...

Thanks Stephan, we are experiencing the same problem, and this addresses the issue nicely.

Fabian said...

THANK YOU! I spent more than 8 hours trying to fix this!

Higgaion said...

well, I'm not as bad off as fabian, but still thanks for saving a lot of my time.

Anonymous said...

Thanks VERY much for blogging about this -- I'm swamped at work, had to get this thingie working, received the same error, did a quick search on google, and found your blog. Thanks again -- Bill

Renso said...

Thanks Steve
At first I couldn't get it working as I placed the NOCOUNT as a first statement inside my EXEC dbo.sp_executesql @statement
sql create script file (I create the schema on the fly during unit testing). As you correctly stated it must appear just before the UPDATE statment and after the AS statement.

Mani Malekmohammadi said...

thanks, I spent 8 hours trying to figure out, NHibernate.AdoNet.TooManyRowsAffectedException

And You saved me great deal of headache. we are using triggers for logs on all of our tables.

Solmaz Ojaghi said...

Tanx for ur solution , it was userfull for me :)

Anonymous said...

Thanks for the solution
But any other solutions???

Anonymous said...

Hello!!!

I tought it could save my life, but now I don't know how can I set NOCOUNT if I'm not using PL-SQL, just NHibernate.


Thank you.
thiagoboccio@gmail.com

Anonymous said...

The god-dmned SINGLE trigger on the database was on MY table.
Thanks.

Anonymous said...

Thanks a lot stephan