Set NOCOUNT OFF at database level?

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 a开发者_运维问答n 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

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

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

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.

You can set nocount property of database ON/OFF from SSMS as well. Please find below

1. Right click on instance and select properties
2. Then select Connections
3. On right pane, you will find list of properties including 'no count'
4. Uncheck this property to make it OFF

Set NOCOUNT is Set at DB level. Execute below queries one after another separately. This gives better idea.

SET NOCOUNT ON 
Update Users set FirstName='XXXX' where Id='YYYY'; 

***Command(s) completed successfully.

SET NOCOUNT OFF
Update Users set FirstName='XXXX' where Id='YYYY'; 

***(1 row(s) affected)