SET NOCOUNT OFF disregarded in SQLCMD

Question

I want to see how many rows are affected for each DDL statement that is run by a query, so I set SET NOCOUNT OFF at the start of each query that is run.

Sample query:

SET NOCOUNT OFF;
GO
BEGIN TRY

    BEGIN TRANSACTION
    UPDATE dbo.tbProvClause SET ClauseTemplate = 'Clause1' where DocumentName = '\Templates\EndorsAccessPlainLanguageQCEng.CDS';
    UPDATE dbo.tbProvClause SET ClauseTemplate = 'Clause 2' where DocumentName = '\Templates\EndorsEnforcedRemovallLtdMktPublicPropertyQCEng.CDS';
    UPDATE dbo.tbProvClause SET ClauseTemplateFR = 'Malgré French Clause 1' where DocumentNameFR = '\Templates\EndorsAccessHOPPQcFr.CDS';
    UPDATE dbo.tbProvClause SET ClauseTemplateFR = 'Malgré les exceptions  Clause 2' where DocumentNameFR = '\Templates\EndorsEnlèvementFTNdomainepublicERLMPublicPropertyQcFr.CDS';  
   COMMIT TRAN
   PRINT 'Script Completed With Success - Changes committed on ' + CAST(current_timestamp AS varchar(25))
END TRY

BEGIN CATCH
   --
END CATCH

GO

and it returns

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Script Completed With Success - Changes committed on Nov 29 2017 12:10PM

This is good. But when I run the same in SQLCMD, I get only 1 row .i.e.

sqlcmd -S testserver -dTestDB -i StackOverflowSQL.sql

(1 rows affected)
Script Completed With Success - Changes committed on Nov 29 2017 12:24PM

How do I retain the ability of the SET NOCOUNT OFF in SQLCMD? The reason I asked this question is that I have a number of scripts that I want to batch using SQLCMD and I will be saving their logs. In this case, the SET NOCOUNT OFF is very useful in checking how many lines of 1 rows affected will give a feedback that the run was successful.


Show source
| sql-server   | ssms   | sqlcmd   2017-11-29 18:11 3 Answers

Answers to SET NOCOUNT OFF disregarded in SQLCMD ( 3 )

  1. 2017-11-29 18:11

    Try something like this and see if it works.

    use -v (small letter v).

    sqlcmd -v NOCOUNT=OFF -S testserver -dTestDB -i StackOverflowSQL.sql
    

    Or

    In the same command prompt first run SET NOCOUNT=OFF before calling sqlcmd .

    Look into below documentation link and search for "Variable Precedence". You will get some idea.

    https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility

  2. 2017-11-29 19:11

    It's just a hunch. Remove the Go statement after "set nocount off".

    First of all "set nocount off" statement does not require a GO. Secondly, I think GO (A batch executor) may be setting the option only for that batch.

    There is a rather stupid workaround if my suggestion does not work. You can: print @@rowcount after every sql statement which you may be interested to monitor the row counts.

  3. 2017-11-29 21:11

    Found the problem. There were multiple versions of SQLCMD installed in the machine. To find out which version I was using:

    E:\Test>where sqlcmd
    C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE
    C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE
    

    The one that I was using was SQL Server 2008 R2 version. the I looked into the system environment variables PATH and changed the order and now it uses the SQL server 2012 version. After changing the PATH

    E:\Test>where sqlcmd
    C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE
    C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE
    
    E:\Test>sqlcmd -S testserver -dTestdb -i StackOverflowSQL.sql
    
    (1 rows affected)
    
    (1 rows affected)
    
    (1 rows affected)
    
    (1 rows affected)
    Script Completed With Success - Changes committed on Nov 29 2017  3:37PM
    

    It works!

Leave a reply to - SET NOCOUNT OFF disregarded in SQLCMD

◀ Go back