CODEWITHSUNDEEP
sql-serverssmssqlcmd
Jags_2000
Jags_2000

Reputation: 41

SET NOCOUNT OFF disregarded in SQLCMD

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.

Upvotes: 4

Views: 4591

Answers (3)

Jags_2000
Jags_2000

Reputation: 41

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!

Upvotes: 0

jujiro
jujiro

Reputation: 300

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.

Upvotes: 0

Pavan Chandaka
Pavan Chandaka

Reputation: 12751

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://learn.microsoft.com/en-us/sql/tools/sqlcmd-utility

Upvotes: 2

Related Questions