SQL Server 2005 – Creating SQL Job to Send Email in Excel Format

sql-server-2005

I am not skilled in SQL and need some help. I have a SQL2005 server and I created a job to send an email with a CSV attachment. My job runs fine but I need the end result to be an Excel file in either .xls or .xlsx format.

Here is the code for the job:

USE My_DB
GO

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DBMail'
    ,@recipients =  'me@my_org.com' 
    ,@copy_recipients = ''
    ,@execute_query_database = 'My_DB'
    ,@query = 'exec MY_RPT_MonthlyWIPSp'
    --@body =@bodyContent
    ,@subject = 'Monthly WIP Report'
    ,@attach_query_result_as_file = 1
    ,@query_attachment_filename = 'result.csv'
    ,@query_result_separator=',',@query_result_width =32767
    ,@query_result_no_padding=1;

What do I need to change and where to get the data to come out in the format I need? Do I have to modify my SP?

Best Answer

tl;dr I don't think what you want is an option with the tools you have available.

I do not believe it is possible with SQL 2005, to actually have your data export as .xls or .xlsx format.

Over the years I have tried with several different tools to do just this. I did not have the option to select different tools.

In anycase, for most window devices opening a .csv file will open it in Excel. Normally the .csv file is physically smaller then it would be as .xls or .xlsx, so there are some benefits to keeping it .csv.

Once you have the .csv file you can open it with Excel and save it as .xls or .xlsx. I think that is your only option.

Update This is outside the parameters of the question but worthy of mention: There is a PowerShell module dfinke/ImportExcel that will allow you to convert table data to Excel even if you don't have Excel on the machine, and put in a folder. I saw this in a demo of Dbatools and it looks really interesting.

Related Question