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.
If I run same from SQLCMD prompt, I am seeing below
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.
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”.
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)
9 Comments. Leave new
SSMS is yu interface where as Sqlcmd could be used for batch scripts and no output is preferred
may be you are right but I am not sure.
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.
Thanks for your comment William!
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
That makes sense.
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
Thanks for your comment Dave.
Hi why is that when i update a single row in database it shows 3x (1 row(s) affected). Btw this is MS SMS