Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

up vote 7 down vote accepted

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.

share|improve this answer
add comment

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 from http://unxutils.sourceforge.net/ The 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).

share|improve this answer
    
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
add comment

Don't know if someone still needs this but I did. So I tried and it's pretty straight forward.

sqlcmd -h -1 -s "," -W [...]

Where :

  • -h -1 gets rid of header
  • -s "," set the comma as column separator
  • -W is needed to remove trailing space
  • [...] is the remaining of your sqlcmd
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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