SQL SERVER – Where is Rows Affected in Output?

There are so many configuration options in SQL Server that it is difficult to keep track of them. I was recently hit by one of such option and it took little time for me to figure out the place to change it. So share my findings about Rows Affected via this blog post.

In SSMS, I was running a simple query as below

SELECT COUNT(1)
GO

When I run its from SSMS, here is what I see.

SQL SERVER - Where is Rows Affected in Output? nocount-01

If I run same from SQLCMD prompt, I am seeing below

SQL SERVER - Where is Rows Affected in Output? nocount-02

As you can see I am not seeing “(1 row(s) affected)”. Initially, I thought it was an issue with SQLCMD so I connected to a different instance of SQL Server and I was surprised to see the result.

SQL SERVER - Where is Rows Affected in Output? nocount-03

So, I executed the same command on a different SQL instance using same SQLCMD and I was seeing what I expected.

This can only happen if “set nocount on” has been run by the connection.

While searching, I realized that it is a setting which can be set on the connection level.

  • Connect to the SQL Server under question via SQL Server Management Studio.
  • Right click on the Server Name > Properties > Connections and in the “Default connection options” uncheck “No count”.

SQL SERVER - Where is Rows Affected in Output? nocount-04

Once I unchecked it, SQLCMD also showed rows affected.

Here is the question: Why the settings are not applied to SSMS but only to SQLCMD? Write a comment and let us share.

Reference: Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Instance Definition Working with Oracle and SQL Server
Next Post
SQL SERVER – Management Studio – Changing Status Bar Location

Related Posts

9 Comments. Leave new

  • SSMS is yu interface where as Sqlcmd could be used for batch scripts and no output is preferred

    Reply
  • William Clardy
    March 3, 2016 6:00 pm

    It’s really simple: unlike SQLCMD, SSMS has its own array of user-configurable settings. To set NOCOUNT on in SSMS, you go to Tools|Options|Query Execution|SQL Server|Advanced in order to configure the default settings for query windows.

    You can also adjust each query window individually by right-clicking in the window and selecting Query Options.

    Reply
  • In Management Studio when you performed the set “nocount on” as a default (your screenshot above), this was a server level setting.
    exec sp_configure ‘user options’, ….

    this will affect all clients that connect and accept the servers setting.

    Management Studio sends its own SET statements upon connection so any ‘user options’ set using sp_configure will be overwritten.
    SQLCMD accepts Server level user options upon connection

    Reply
  • It states “default connection options” not just “connection options”. The word “default” implies it can be overwritten.

    I think the weird thing is that it is set in SSMS and yet SSMS also has an override for it in its own options. This is understandable and as it should be as you can set the default for all connections as well as the local setting for local (to SSMS) setting but I agree it feels odd to do both in the same app.

    All is good with the world

    Dave

    Reply
  • Leszer E. Colocado
    May 25, 2016 12:10 pm

    Hi why is that when i update a single row in database it shows 3x (1 row(s) affected). Btw this is MS SMS

    Reply

Leave a Reply

Menu