Mail Attachement

SQL Server Administration
1

Hi All,
I need to send one Excel report on a daily basis to certain users on mail. Is there a way if i can achieve this via SQL

Please suggest

2

You can use sp_send_dbmail

You should create a stored procedure that sends out the e-mail, and then schedule to run that stored procedure using a SQL Server Agent job. There are lot of examples and tutorials available on the web on how to use sp_send_dbmail and about how to set up agent jobs.

If you run into difficulties, reply.

3
  1. This task can be achieved using SSIS Package.
  2. If you want to do in T-SQL. Create a linked server for the excel file. and insert data from table and then send the excel as attachment in mail.
1 Like
4

Come to think of it, I would prefer doing this using SSIS rather than T-SQL as well. When OP said SQL,my mind got stuck on T-SQL.

5

Not all servers will have Integration Services installed - so the OP may not have SSIS available as an option. One issue with using sp_send_dbmail is that you are not actually sending an Excel file. You would be sending a delimited file (tab or csv) that will open in Excel.

If you don't take that into consideration - the file will not be able to be opened directly. To fix that issue, you can tell Excel what the delimiter is - and then it will open.

To do that...in your query you need to modify the first columns name to include the delimiter - with this syntax:

Select sd.Name As [Sep=,' + char(13) + char(10) + 'Name]

Another option - if it is available - would be SSRS. I find sending Excel spreadsheets generally is easier using an SSRS report and subscription. It delivers in Excel and you can format it however you need it...

6

This is true for the @query_attachment_filename parameter, but the @file_attachments parameter allows you to attach any file, including a native Excel file.