Set NOCOUNT OFF at database level?

Question

Hello all. Can I set NOCOUNT to be OFF by default for a particular database?

I need it for NHibernate (if it's ON, I get exceptions when saving). I can see the setting to disable it for an entire server (where it's set ON), but we're sharing the server with other databases. Is there a way I can set it to OFF just for my database?

Or is there an NHibernate setting that will do this for me? (I've worked around it for now with a custom DriverConnectionProvider that issues the command whenever it creates a connection. Works ok, but feels a bit awkward)

I'm using SQL Server 2000, btw.

Thanks Matt

Answer 1

no, there is no database wide setting for SET NOCOUNT.

same question: http://www.eggheadcafe.com/software/aspnet/32120672/set-nocount.aspx

Answer 2

You could use the NH IInterceptor interface's OnPrepareStatement method to modify the NH-generated SQL statements to include a "set nocount off" statement at the top of every sql query issued by your application.

Some bloke called Dave reckons it's "evil" to do so, but it might be your answer. The link takes you to the page on his blog describing how to implement it.

My reservation about doing so is you would be creating a dependency on your application running against a database server that understands "set nocount off" - in other words, you'd be ignoring the configured dialect.