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?


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.

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

  • I only meant to remove the last line. The numbers are part of the output and I need them. As much as possible i would like to do this without installing new software as this fix might need to happen on other boxes. Thanks for pointing this out though, i sure could use unix utilities, tail would probably suffice. – rumz Aug 9 '10 at 21:37
  • I updated my original answer. – jftuga Aug 9 '10 at 21:44
  • Should i just copy all the unix applications into C:\Windows\system ? – rumz Aug 10 '10 at 1:01
  • I like to create a c:\bin, and then change the PATH environment variable to include it. This way, I can easily see which programs I have. There are just too many files underneath the c:\windows tree to keep up with otherwise. – jftuga Aug 10 '10 at 2:46

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)
-- SQLCMD.exe batch command
-- To Reenable
**  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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.