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