Opinion Question

[Besoup]

I have an end user that gave me a bunch about 9 different criteria that they would like to see in a bi-weekly report, preferably emailed to him automatically. I have created all of the SQL queries needed in the Query Analyzer and quite honestly he would be happy with just the results in the format that it produces when you send the results to file which produces a .rpt file. So basically I am looking for the best way to set this up as an scheduled email. I am using MS SQL 2000.

Do I have to write an app to throw in the scheduled tasks of the server?

I saw something about maybe using xp_sendmail but not quite sure what I would do there. Would I have to break up my batch SQL to separate stored procs? If I went the xp_sendmail route how would I trigger it bi-weekly?

Is it time for some professional SQL training? :P

Thanks for any input.

[alex_read]

I had a feeling it was a bit easier than this and imagined there was a built in way to E-mail a reporting services report. There is a way, as described right at the bottome of this page, but it's not quite as straightforward as I thought it'd be. Nonetheless, this is one option for you:

http://www.ssw.com.au/ssw/Standards/...x#Subscription

[FunkyDexter]

I've never used sp_SendMail but provided you can get your head around how to do that you should just be able to create a maintenance plan to run it regularly:-
In management studio open the management branch of the object browser.
Right click on maintenance plans and create a new plan.
Create an apropriate schedule for it (should be self explanatory when you look at the screen).
Drag an Execute TSQL task into the plan and enter your xp_SendMail command into it.

I did notice that xp_SendMail is due to be phased out, though, and MS recommend you use Database Mail instead.