Better way to automatically email data driven Sql Server Reporting Service reports?

We have lots of SSRS reports that we would like to automatically deliver via email to employees based on data in the database. SSRS has the data driven reports feature, but the key missing component is any way to indicate that a report was successfully processed/sent.

For example, lets say that some event triggers a record to go into a database table saying that employee X needs to get a copy of the cost report for project Y.

It's very easy to schedule a data driven subscription for the cost report t开发者_C百科hat gets X his report, there is apparently no way to then flag the X-Y record as "Don't send this report again".

We could, and in some instances have, changed the SP that selects the data for the report to also make this update, but the result is that if there is a problem with the report, or a problem delivering the email (say for example the network is down) then the system thinks the report has been sent when it hasn't.

And yes, we could, and in some instances have, written our own little services/apps to read the DB, grap the report, send it in an email, and update the database again, but this seems like something that shouldn't be as difficult as it is.

I'm looking for any suggestions here. This must be one of the most common things people want to do with reports, but I see nothing anywhere about addressing this problem, or products we could buy to remove the shortcoming, or open source projects we could use etc. It seems like we are left with either totally rolling our own system, or sticking with a really crappy method for delivering these reports.

Caveat: I've only got access to SQL Server 2005 & 2008 R2 Standard editions. I'm not lucky enough to have access to the Enterprise editions.

However, I know you can check the LastStatus and LastRunTime fields in the Subscriptions table of the ReportServer database (I presume that data driven subscriptions would still store subscription data in the Subscriptions table).

With this, you could create and schedule a stored proc that checks the status and last send time of the subscription(s), and if it needs to be sent again, you can pass the GUID for the subscription to the sp_start_job stored proc, which will send the subscription as normal. You would probably need to adjust the subscription so it won't send itself - only when triggered from your scheduled stored proc.

It's something I've used in the past, but once you get to having hundreds of subscriptions, it can be cumbersome at best to manage.

you can look in msdb if you are looking to see whether a person was sent a specific report. This might give you an idea, it's off one of the system views, but there are also other system views related to attachments and whether the email is in an unsent status. This is if you are utilizing database mail..

chris `

use [msdb]

GO

select

recipients ,copy_recipients ,blind_copy_recipients ,subject ,body ,file_attachments ,sent_status ,convert(varchar ,sent_date,109) as Date_Sent

from dbo.sysmail_sentitems `

Can you not just deliver reports to a central file share instead, then tell the users that's where the reports are. We went down this route to get round the issue of mailbox's full, issues with email etc.