Sqlcmd to generate file without dashed line under header, without row count

Solutions:

1) To remove the row count ("(139 rows affected)") you should use SET NOCOUNT ON statement. See ref.

2) To remove column headers you should use -h parameter with value -1. See ref (section Formatting Options).

Examples:

C:\Users\sqlservr.exe>sqlcmd -S(local)\SQL2012 -d Test -E -h -1 -s, -W -Q "set nocount on; select * from dbo.Account" > d:\export.txt. 

or

C:\Users\sqlservr.exe>sqlcmd -S(local)\SQL2012 -d Test -E -h -1 -s, -W -Q "set nocount on; select * from dbo.Account" -o "d:\export2.txt"

The guy with the top answer didn't answer how to remove the dashed line. This is my awesome solution.

  1. First include -h -1 which removes both the dashed line and header
  2. Then before your select statement manually inject the header string that you need with a PRINT statement. So in your case PRINT 'acctnum,rptmonth,facilname' select..*...from...

Sorry I'm 4 years and 9 months late.


Use the following;

sqlcmd -S . -d MyDb -E -s, -h-1 -W -Q "set nocount on;select 'account','rptmonth', 'thename';select account,rptmonth, thename from theTable"  
> c:\dataExport.csv
  • remove the header -h-1
  • remove row count [set nocount on;]
  • add header select [select 'account','rptmonth', 'thename';]
  • add your select [select account,rptmonth, thename from theTable;]