When automating SQL Server tasks with sqlcmd, the default behavior includes displaying the "(X rows affected)" message after each query execution. This becomes problematic when:

• Generating clean CSV output for data processing
• Creating machine-readable output files
• Integrating with other applications that expect pure data

Here are three reliable approaches to eliminate the row count message:

1. Using SET NOCOUNT ON

Modify your SQL script to include this directive at the beginning:

SET NOCOUNT ON;
SELECT x, y, z FROM agent;

This is the most reliable method as it prevents SQL Server from sending the row count message to the client.

2. Redirecting Error Output (Alternative Approach)

While not as clean as SET NOCOUNT ON, you can redirect the messages to NUL:

sqlcmd -S klingon -d stardb -i C:\testscript.sql -o C:\testresults.csv -h -1 -s "," 2>NUL

Note: This might suppress other important messages too.

3. Using bcp Instead of sqlcmd

For pure data export scenarios, bcp might be a better alternative:

bcp "SELECT x, y, z FROM agent" queryout C:\testresults.csv -S klingon -d stardb -c -t, -T

Here's a full implementation that produces clean CSV output:

-- testscript.sql contents:
SET NOCOUNT ON;
SELECT 
    CAST(x AS VARCHAR(10)) + ',' + 
    CAST(y AS VARCHAR(50)) + ',' + 
    CAST(z AS VARCHAR(50))
FROM agent;

-- Command line execution:
sqlcmd -S klingon -d stardb -i C:\testscript.sql -o C:\testresults.csv -h -1 -W

For more control over the output format, consider these additional parameters:

sqlcmd -S klingon -d stardb -Q "SET NOCOUNT ON; SELECT x, y, z FROM agent" 
-o C:\testresults.csv -h -1 -s "," -W -w 1024

Where:

- -W removes trailing spaces

- -w sets the line width

- -Q executes a query directly instead of using an input file

When using sqlcmd to export query results to CSV, you'll notice the tool automatically appends metadata like "(X rows affected)" to your output. This becomes problematic when you need clean data files for further processing.

The most effective approach combines several sqlcmd parameters and SQL tricks:

sqlcmd -S klingon -d stardb -Q "SET NOCOUNT ON; SELECT x, y, z FROM agent" -o C:\testresults.csv -h-1 -s"," -W

• SET NOCOUNT ON: Suppresses the "rows affected" messages
• -h-1: Removes column headers
• -s",": Sets comma as the column separator
• -W: Removes trailing spaces from output

For more complex formatting needs, consider adding these parameters:

sqlcmd -S server -d db -Q "SET NOCOUNT ON; SELECT 
    QUOTENAME(x,'"') AS x, 
    QUOTENAME(y,'"') AS y,
    z 
FROM agent" -o output.csv -h-1 -s"," -W -k1

When dealing with NULL values or special characters:

sqlcmd -S server -d db -Q "SET NOCOUNT ON; SELECT
    ISNULL(CONVERT(VARCHAR, x), 'NULL') AS x,
    REPLACE(y, ',', ';') AS y,
    z
FROM agent" -o output.csv -h-1 -s"," -W

For large datasets, consider using BCP instead:

bcp "SELECT x, y, z FROM agent" queryout C:\output.csv -c -t, -S server -T -h "SET NOCOUNT ON"