sqlcmd: no rows affected

Question

When I run exec dbo.subprogram from SQL Server Management Studio, it executes and returns result as many rows affected.

But when I run from cmd C:\Users\mssadmin>sqlcmd -S servername -d databasename -q "EXIT( exec dbo.subprogram)" it returns as 0 rows affected.

What could be the problem?

Answer 1

If you really want to return the number of rows, You need to create a text file A.SQL:

exec dbo.subprogram; 
:EXIT(select @@rowcount)

Because EXIT must be at the beginning of a line, so you can't use -Q. Then you call:

C:\> SQLCMD -S servername -d database -i A.SQL -o A.OUT

Then the returned values by EXIT will be in ERRORLEVEL... You can query with:

C:\> ECHO %ERRORLEVEL%