Sql-server – way to run sqlcmd to get query output without showing the number of rows affected

sql serversql-server-2005

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?

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.

Related Topic