msdb.dbo.sp_send_dbmail - excel attachment w/multiple sheets

Question

msdb.dbo.sp_send_dbmail - excel attachment w/multiple sheets

adrabinski

Mr or Mrs. 500

Points: 533
More actions
January 13, 2010 at 7:35 am

#386091

hello,
I'm using the msdb.dbo.sp_send_dbmail that emails a list of data in an excel attachment. Then i have another sp_send_dbmail that emails counts of the same data as an excel attachment.
Suppose I have multiple related queries and would like for each result set to show up on separate worksheets in an excel attachment, is this possible? If so, how would I specify the results to it's worksheet?
It makes no sense to have a sp_send_dbmail to send an email with a listing of data, then another sp_send_dbmail send another email with counts...it would be great to have all the info in one excel, but different sheets.
Thank you

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 1

I don't know of any way to do what you want by directly calling the procedure. You can create a report in SSRS that will render on multiple sheets, schedule the report to run and deliver the results through email.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

paulm-510034 SSC Enthusiast Points: 136 More actions · Answer 2

Hi,

I've used SSIS to write the data to separate sheets.

So you have an Data Flow task with an OLE DB source with your query/queries and then an Excel Destination where you specify the sheet name.

One caveat....as well as having the xls file in your connection manager I had a XLT(template) as well. The template file holds all the file headings which I copy over the XLS file before I run the Data flow task. I found this necessary, otherwise it tended to append data from the last run, and your current data ended up half way down the file.

If you're not familiar with SSIS then this may not make sense. I can email or post(not sure how) a package example if necessary.

Regards,

Paul

adrabinski Mr or Mrs. 500 Points: 533 More actions · Answer 3

Thanks for the responses. I created a template spreadsheet and used SSIS to populate the values in separate sheets. Then used msdb.dbo.sp_send_dbmail to email the spreadsheet. A round about way, but it works. 🙂

Thanks Again

Aliesha