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)