Export CSV using BCP SQL Agent Job

Ever need to export a CSV from SQL Server in  a friendly easy to consume format? Typically when I try to export a CSV I have to jump through some hoops. I stumbled on this pretty straightforward method to export a CSV using BCP. The result will be a CSV file with a header row emailed to the user. One caveat is that xp_cmdshell must be enabled.

USE [DBName]
CREATE TABLE ##TempTable
(
col1 VARCHAR(20),
col2 VARCHAR(100),
)

INSERT INTO ##TempTable
SELECT col1, col2
FROM Table1

--First we create a header file, then we create the data
--file using a select on our temp table. Finally we run a
--copy command to mash em together.
EXEC xp_cmdshell 'bcp "select ''Col1'',''Col2''" queryout c:\Scripts\header.csv -c -t, -T -E -S SERVERNAME'
EXEC xp_cmdshell 'bcp "select * from ##TempTable" queryout c:\Scripts\body.csv -c -t, -T -E -S SERVERNAME'
EXEC xp_cmdshell 'copy /b c:\Scripts\header.csv+c:\Scripts\body.csv c:\Scripts\report.csv'

--Email the mashed up file
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Profile1',
@recipients = 'dude@cool.com',
@body = 'See attached report',
@subject = 'Sweet Subject Goes here',
@file_attachments = 'c:\scripts\yourfile.csv'

DROP TABLE ##TempTable