SQL Agent Job - .csv file - needs headers

Question

Hello! I have a SQL Server agent job that runs a stored procedure and then appends the date to the filename. The file is .csv. I need the headers to be included in the .csv file but can't figure out how to do that. Can anyone help me? My job is below. Thanks!

DECLARE @dir varchar(8000), @date_str varchar(8000),

@FileName varchar(8000), @bcpCommand varchar(8000), @sql varchar(8000)

SET @sql = 'EXEC dbname.dbo.Rpt_stored proc

SET @dir = '\\pathtofile\'

SET @date_str = REPLACE( CONVERT(char(8),GETDATE(),1), '/', '-' )

SET @FileName = @dir + Filename_' + @date_str + '.txt'

SET @bcpCommand = 'bcp "' + @sql + '" queryout "' + @FileName + '" -T -c -t"|"'

EXEC master..xp_cmdshell @bcpCommand

---

Linda

Answer 1

Have a look at the solutions here:

http://stackoverflow.com/questions/1355876/export-table-to-file-with-column-headers-column-names-using-the-bcp-utility-an

Some reason you don't want to build an SSIS package and execute that with the agent?

---

Chuck

Answer 2

Please try this..

declare @bcpCommand nvarchar(1000)
SET @bcpCommand='bcp AdventureWorks.HumanResources.Department format nul -c -f Department-c.csv -T'
EXEC xp_cmdshell @bcpCommand

Reference : http://msdn.microsoft.com/en-us/library/ms191516.aspx

Answer 3

I tried this and I couldn't get it to work with Queryout. Thanks for the suggestion. Linda

---

Linda