7

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?

3 Answers 3

12

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.

2

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

4
  • 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, 2010 at 21:37
  • I updated my original answer.
    – jftuga
    Aug 9, 2010 at 21:44
  • Should i just copy all the unix applications into C:\Windows\system ?
    – rumz
    Aug 10, 2010 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, 2010 at 2:46

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .