Report automation and distribution via Jobs on the Server

Question

Report automation and distribution via Jobs on the Server

tombynum

Mr or Mrs. 500

Points: 597
More actions
January 19, 2004 at 7:44 am

#85432

I am wondering how some of you SQL veterans have handled report automation and distribution.
Let me start by saying...I just learned about the upcoming "Reporting Services" so please don't respond with "...go buy Reporting Services..."
What I want to do is release (through email) small bits of information in the evenings via Jobs running on the server. Let's say they are "statisitcal" in nature, but not big time data analysis...just little summary type snapshots of data like # of Sales summarized by SalesID with Total $'s.
The idea is....the manager comes in, opens his/her email and there's a little snapshot of yesterday's sales activity. It doesn't even have to be a "report" (like formal page layout, etc) It could just be the output of a query.
Once again, I realize this might be part of this new "Reporting" service...but surely some of the veteran developers have bridged this gap...how'd you do it?

The ~BEST~ solution is always the simplest one!

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

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

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 1

You could create an stored procedure wich executes the report you need and send the email to someone in all the servers you need.

Then in a central server or administration server, set it up as a master server and add all your target servers, Then create a job wich executes the sp you created before and schedulle it centrally.

The idea of adding jobs from a central location is to manage the administration of job. If you have few servers, then it wom't be necesarelly. You just can create manually the same job in all the servers.

tombynum Mr or Mrs. 500 Points: 597 More actions · Answer 2

Administration: I only have one server so that's easy

Reports: I am not talking about administrator reports...I am talking about end user type stuff...my original email mentioned a Sales report that is emailed to managers.

Please explain your phrase "...stored procedure that executes THE report..."

What do you mean by "THE report" As far as I know there is no "report" object in SQL server, so you must be speaking of an outside reporting tool (*of which there are many*).

Are you talking about automating an outside tool via the SP?

The ~BEST~ solution is always the simplest one!

philcart SSC-Forever Points: 47794 More actions · Answer 3

I think what racosta was getting at is that you have a query that returns the results you want and then those results are emailed using xp_sendmail. You would have something like the following in a Scheduled Job.

EXEC xp_sendmail @recipients = 'SalesManager', 
   @query = 'SELECT TOP 10 Name, Value FROM Sales',
   @subject = 'Top 10 Sales People',
   @message = 'The top 10 Sales people from yesterday.',
   @attach_results = 'TRUE', @width = 250

This is very basic "reporting" and will only provide you with the simplest of text reports. You won't have any formatting options and you can't put the information into another format like Excel.

--------------------
Colt 45 - the original point and click interface

tombynum Mr or Mrs. 500 Points: 597 More actions · Answer 4

Got it!

My apologies to racosta, I didn't read the xp_sendmail specs in Books Online.

I've already tested and it's exactly what I wanted. This is for basic alerting and things like that...nothing fancy.

thanks to you both for your help.

The ~BEST~ solution is always the simplest one!