1

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?

  • You might want to google 'why not use SQL 2005' I am sure there are lots of reasons you are still using it.... The reasons not to are significant. – James Jenkins Jan 10 '19 at 16:19
  • Unfortunately I am stuck with SQL 2005 for a few more months until we can get a legacy system upgraded. – Kevin H Jan 10 '19 at 16:22
  • Learned something new and updated my answer. If you REALLY need to get to Excel, it would provide an option, particularly if you have existing PowerShell skills. – James Jenkins Feb 7 '19 at 17:56
  • Fairly easy to use SQL Server Reporting Services (SSRS), which comes with SQL Server from 2005 and up. Create a report, and create a subscription to the report, which you can specify as Excel. – HardCode Feb 7 '19 at 19:02
1

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.

  • 1
    Looks like that is the case, on my tests on SQL Server 2014 SP2, the .xls attachment mail failed, the .xlsx mail succeeded but the file was 'damaged' and unable to open. – Randi Vertongen Jan 10 '19 at 14:47
  • @James I found that sending the file as a .csv did not work the way I needed it; however, you made me think of another solution. I changed the delimiter from comma to tab and the file opens with the data formatted properly. – Kevin H Jan 10 '19 at 16:25
  • @KevinH I have done similar as well when my data had comma's in it. I have also found tabs in my data (i.e. comments of a text field) There are other options for delimiter it has been a few years, but I think I used ^ (Caret , upper case 6) when I found tabs in my data. – James Jenkins Jan 10 '19 at 16:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.