Home / server / Questions / 168657
Accepted
rumz
Asked: 2010-08-10 12:27:53 +0800 CST

Is there a way to run sqlcmd to get query output without showing the number of rows affected?

  • 772

I am running a simple query in SQL Server 2005 and want it to export to a file via sqlcmd. I was hoping to get the results in a csv format without the headers and the query metadata (how many rows are affected). for the headers you can actually specify -h -1 but how can you get rid of the ending text?

Right now i have

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

with the script being something simple to the tune of

select x, y, z from agent

Unfortunately, results are like so:

         24 aingles1         creablegs            
         25 tbails12         bull2dog12           
         26 jtaylor3         Leandon62606         
         27 forrestw1        nuke19211            

(4 rows affected)

I can't seem to find anything in the help file that will tell me how to remove the last part which tells me how many rows are affected.

Ideas anyone?

sql-server sql-server-2005

3 Answers

  1. Best Answer

    I think you might want the "SET NOCOUNT ON" option. Your SQL script will look like: 

     set nocount on  
 select x, y, z from agent  
 set nocount off

    And the results set will be: 

     24 aingles1         creablegs            
 25 tbails12         bull2dog12           
 26 jtaylor3         Leandon62606         
 27 forrestw1        nuke19211

    Minus the count of rows line at the end.

    • 10 
  2. sqlcmd -S svr -d db ... | findstr /v "rows affected"

    findstr is built into the OS and is simliar to grep. See findstr /? for more options.

    To remove a column, use cut.exe tool. So you could run:

    sqlcmd -S svr -d db ... | cut -c10-

    This would only output from character 10 onwards, thus removing the line numbers. Adjust the number 10 accordingly. You could also try fields:

    sqlcmd -S svr -d db ... | cut -f2-

    This would only output fields 2 and onwards (to the right).

    • 2

  3. I add the following code block to the beginning of the query itself to remove all messages when using sqlcmd.exe to export results to CSV.

    -- Suppress warnings and messages like (2 rows effected)
SET ANSI_WARNINGS OFF;
SET NOCOUNT ON;
-- SQLCMD.exe batch command
:setvar SQLCMDERRORLEVEL 1
-- To Reenable
--:setvar SQLCMDERRORLEVEL 0
/**********************************************************************************
**  DISABLING WARNINGS AND MESSAGES
**  Disable "Changed database context to 'DatabaseName'." message in the CSV file when using 
**  SQLCMD.exe. You'll get a syntax error when executing this query in Management 
**  Studio if SQLCMD Mode is not enabled [Query] --> [SQLCMD Mode].
**  :setvar SQLCMDERRORLEVEL 1 is used to disable messages like (152 Rows affected).
**********************************************************************************/
    • 1