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 :
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.
[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.
Comments:
Links to this post:
<< Home
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
# posted by 
: 4/09/2008 9:15 PM
: 4/09/2008 9:15 PM
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.
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.
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.
And You saved me great deal of headache. we are using triggers for logs on all of our tables.
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
Post a Comment
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
# posted by : 3/02/2009 9:43 PM
: 3/02/2009 9:43 PM Links to this post:
<< Home
|
|
|
