• Using column names from SQL table as column headers
• Remove existing data from the old excel file
• Expanding the column width so that all the data is visible when the file is opened. The reason why this is important is because the same excel file is used in a MS Word document as a linked object and if the columns are not wide enough in excel then data stays hidden in the Word file thus not coming out in print outs

create procedure proc_generate_excel_with_columns
(
	@db_name	varchar(100),
	@table_name	varchar(100),	
	@file_name	varchar(100)
)
as

--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
select 
	@columns=coalesce(@columns+',','')+column_name+' as '+column_name 
from 
	information_schema.columns
where 
	table_name=@table_name
select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''
exec(@sql)

--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''
exec(@sql)

--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
exec(@sql)

--Delete dummy file 
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql)

Description                                                                                Prev_Count  Curr_Count  Diff
------------------------------------------------------------------------------------------ ----------- ----------- -----------
---------------------------------------------------------------------    	                 NULL        NULL        NULL
DataFileformatForMIX                                                                       NULL        NULL        NULL
AllBreeds current::102_Live; previous:101_Live                                             NULL        NULL        NULL
DataFileformatForMix: number of records:                                                   124272743   126012029   1739286
Number of animals:                                                                         7778431     7855380     76949
Number of dropped animals (just group of 4)                                                NULL        3703        NULL
Number of gained animals (just group of 4)                                                 NULL        80652       NULL
Number of DROPPED <group-of-4, lactation and DIM> values                                   NULL        94446       NULL
Number of GAINED <group-of-4, lactation and DIM> values                                    NULL        1833706     NULL