SQL Server 2008: How to export SQL data to CSV file with headers using BCP

[kwilliams]

I created a simple Stored Procedure (SP) that queries a table and renames the column names, and I'm able to push those results out to a CSV file *with* those header names when done manually.But when I try to export the results to a CSV file by running the same SP using xp_cmdshell with bcp, the header row (Col1, Col2, and Col3) does not appear in the resulting CSV file.ORIGINAL TABLE:ColumnName1-----ColumnName2-----ColumnName3Joe Schmo CustomerJane Doe CustomerTim Tiny MusicianQUERY WITHIN SP:SELECT ColumnName1 AS Col1, ColumnName2 AS Col2, ColumnName3 AS Col3FROM TABLENAMEQUERY RESULT:Col1-----Col2-----Col3 <<<<----- WHAT I WANT!Joe Schmo CustomerJane Doe CustomerTiny Tim MusicianHere's the code I'm using within the job:EXEC xp_cmdshell 'bcp "EXEC DATABASENAME.dbo.STOREDPROCEDURENAME" QUERYOUT "\\MYSERVERNAME\files\export.csv" -c -t, -T -S'CSV FILE RESULT USING BCP:Joe Schmo CustomerJane Doe CustomerTim Tiny MusicianAs you can see the headers are missing. What am I doing wrong?

[justsomeguy]

I created a simple Stored Procedure (SP) that queries a table and renames the column names, and I'm able to push those results out to a CSV file *with* those header names when done manually.

What do you mean "when done manually"? How exactly do you do it? Column names are not part of the rows that a query will return, so it sounds like it is only exporting the data that is returned. You may need to have the SP output the column names first, then output the data. You can use a UNION query to do that. The limitation is that you'll need to case all columns as character types if they aren't already, because the types need to match the text column names.