SET NOCOUNT ON at instance level - not working.

Question

SET NOCOUNT ON at instance level - not working.

GroverVivek

SSCommitted

Points: 1676
More actions
August 11, 2017 at 12:59 am

#402417

Hello Members,
I have configured nocount as default connection property using following command.
EXEC sp_configure 'user options', 512.
Unfortunately, when I connect to SSMS and run query or procedure, number of row affected are still visible in messages. I still have to explicitly set nocount on for the session. Could you please clear my doubt that why is it happening and is it better to switch nocount on at instance level instead of tell all developers to user set nocount on in each procedure they create?
Thanks in advance.
Regards
VG

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

It's not an instance-level setting. It's a session-level setting.
The user options is, afaik, a default for clients that don't specify the setting, and SSMS does specify it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GroverVivek SSCommitted Points: 1676 More actions · Answer 2

GilaMonster - Friday, August 11, 2017 1:08 AM
It's not an instance-level setting. It's a session-level setting.
The user options is, afaik, a default for clients that don't specify the setting, and SSMS does specify it.

Thanks Gail
I am wondering that what could be the reason that a DBA cannot never change that setting for the instance or database? What could be hurdles if we set the setting at instance level and get rid of setting it on in each stored procedure that developer write?

Regards
VG

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

As I said, setting user_settings just provides a default for clients that don't specify it when connecting. It's a session-level setting, and hence it has to be set by the session.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GroverVivek SSCommitted Points: 1676 More actions · Answer 4

GilaMonster - Monday, August 14, 2017 5:28 AM
As I said, setting user_settings just provides a default for clients that don't specify it when connecting.

Now, let me explain what I did last time. I checked that nocount connection setting to on using properties of instance. Then, I started a session and wrote select command on a table. I was expecting that it should now show number of rows affected in messages. But it returned number of rows returned. As I didn't specify the session level setting so that setting would be default from connection settings.

Regards
VG

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

GilaMonster - Friday, August 11, 2017 1:08 AM
The user options is, afaik, a default for clients that don't specify the setting, and SSMS does specify it.

This is what SSMS runs when a new query window is created.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GroverVivek SSCommitted Points: 1676 More actions · Answer 6

Thanks Gail. Makes sense. But why default connection options are available to choose for?

Regards
VG

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

GilaMonster - Friday, August 11, 2017 1:08 AM
The user options is, afaik, a default for clients that don't specify the setting, and SSMS does specify it.

Not all clients specify settings. When they don't, the user options take effect. When they do (like SSMS does), the user options are overridden.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GroverVivek SSCommitted Points: 1676 More actions · Answer 8

So, following is what is the nutshell of our discussion:

1. Whenever, we code execute commands in SSMS. It doesn't matter what user option is selected in default connections properties. Session level setting override default connection properties.

2. User options take into effect when ever code is executed from Application within application code like .net/.java/etc.

Please confirm. And thanks a lot for giving your time to this post.

Regards
VG

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

SQLearner@vgrover - Wednesday, August 16, 2017 11:45 PM
1. Whenever, we code execute commands in SSMS. It doesn't matter what user option is selected in default connections properties. Session level setting override default connection properties..

No.
When we make connections from SSMS, it specified its own defaults for session properties. You can change what it specifies in the SSMS options. Any that it doesn't specify will be taken from user_options

2. User options take into effect when ever code is executed from Application within application code like .net/.java/etc.

No. User options take effect when a connection is made and a session setting is not explicitly specified on connection by the driver/client.

These are session-level settings. They're set at the session level, they apply at the session level.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass