Automate emailing of sql data/report

[Dan777]

First off, please bear with me if you think this question shouldn't be in this forum. Because of the nature of the question it's rather ambiguous about where it should be.

The question is, I have to email a report of data from a sql 2005 database to people once a week. The powers that be would like this automated as much as possible. Does anyone have any awesome/slick ideas on how to implement this?

I've got a sql stored proc created to get the data from the DB and tweak it to be ready for display in a report. Now it's just a matter of how to get it to the recipients. Of course we'd like the means to be quick. And the results should be robust in appearance, (I don't want to just dump the data into the body of an email).

The technologies/tools I have, and have access to, are sql 2005 with Reporting Services, MS Outlook/Exchange, Visual Studio 2005, and Sharepoint.

I've thought about creating a sql job that would:
1. run the stored proc
2. create an email
3. put a report file in the body of an email
4.distribute the email.

I've also thought about
1. putting a report on the companies intranet
2. create a sql job that would
a. run the stored proc
b. refresh the report on the intranet
c. create an email
d. put a link in the body of the email to the report
e. distribute the email.

Any thoughts, suggestions or advice on this is greatly appreciated!

Thanks,
Dan

 

[RiverGuy]

SQL Server Reporting Services has a scheduler built in that will allow you to email a report in a variety of different formats to a distribution list of your choosing. You basically set up the reporting server if you have not done so, create the report, and set the schedule and parameters for the report. It's pretty painless.

 

[Dan777]

Sounds like it has a lot of potential. I'll definately check that out. Thanks RiverGuy!