SET NOCOUNT OFF is ignored in SQLCMD

Question

I want to see how many lines affect each DDL statement that is triggered by a query, so I set SET NOCOUNT OFF at the beginning of every query that is executed.

Request example:

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 he 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

It's good. But when I run the same in SQLCMD, I get only 1 line .ie

sqlcmd -S testserver -dTestDB -i StackOverflowSQL.sql

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

How to save SET NOCOUNT OFF ability in SQLCMD? The reason I asked this question is because I have several scripts that I want to execute using SQLCMD and I will keep their logs. In this case, SET NOCOUNT OFF is very useful when checking how many lines of 1 affected lines give feedback that the launch was successful.

Answer 1

- , .

-v ( v).

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

SET NOCOUNT=OFF sqlcmd.

" ". .

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

Answer 2

. Go "set nocount off".

, "set nocount off" GO. -, , GO ( ) .

, . : print @@rowcount sql, , .

Answer 3

Found a problem. There are several versions of SQLCMD installed on the computer. To find out which version I used:

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 I used was a version of SQL Server 2008 R2. I looked at the PATH system environment variables and changed the order, and now it uses the version of SQL Server 2012. 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!