2

I can't seem to find the right syntax to export data with column names using exec xp_cmdshell bcp in sql server management studio.I've tried the below variations

EXEC xp_cmdshell bcp "select "a_id","b_id","c_id" union select a_id,b_id,c_id from  tablename out 
"\\network_path\a.txt" -c -Uusername -Ppassword -Sservername"

And 

EXEC xp_cmdshell bcp 'select 'a_id','b_id','c_id' union select a_id,b_id,c_id from  tablename out 
'\\network_path\a.txt' -c -Uusername -Ppassword -Sservername'

And

EXEC xp_cmdshell bcp 'select "a_id","b_id","c_id" union select a_id,b_id,c_id from  tablename out 
"\\network_path\a.txt" -c -Uusername -Ppassword -Sservername'

And

EXEC xp_cmdshell bcp "select 'a_id','b_id','c_id' union select a_id,b_id,c_id from  tablename out 
'\\network_path\a.txt' -c -Uusername -Ppassword -Sservername"

I have successfully used the below command to export the table,however I also need the column names.

bcp tablename out "\\network_path\a_test.txt" -c -Uusername -Ppassword -Sservername'
nobody
  • 10,892
  • 8
  • 45
  • 63

1 Answers1

1

Ok you'll actually need to order the header row so it appears on top. This should handle it

EXEC xp_cmdshell 'bcp "select * from (select ''a_id'' as a_id,''b_id'' as b_id,''c_id'' as c_id union select a_id,b_id,c_id from tablename)q order by case a_id when ''a_id'' then 0 ELSE 1 END" queryout "\\networkpath\a.txt" -c -Uusername -Ppassword -Sservername -ddatabasename'
Element Zero
  • 1,651
  • 3
  • 13
  • 31
  • 1
    usage: bcp {dbtable | query} {in | out | queryout | format} datafile [-m maxerrors] [-f formatfile] [-e errfile] [-F firstrow] [-L lastrow] [-b batchsize] [-n native type] [-c character type] [-w wide character type] [-N keep non-text native] [-V file format version] [-q quoted identifier] [-C code page specifier] [-t field terminator] [-r row terminator] [-i inputfile] [-o outfile] [-a packetsize] [-S server name] [-U username] [-P password] [-T trusted connection] [-v version] [-R regional enable] [-k keep null values] [-E keep identity values] [-h "load hints"] – nobody Aug 16 '17 at 19:54