自动通过电子邮件发送数据驱动的 Sql Server Reporting Service 报告的更好方法？
我们有很多 SSRS 报告，我们希望根据数据库中的数据通过电子邮件自动发送给员工。 SSRS 具有数据驱动的报告功能，但缺少的关键组件是指示报告已成功处理/发送的任何方式。
例如，假设某个事件触发一条记录进入数据库表，表示员工 X 需要获取项目 Y 的成本报告副本。
为获取 X 的成本报告安排数据驱动订阅非常容易在他的报告中，显然没有办法将 XY 记录标记为“不要再次发送此报告”。
我们可以（在某些情况下）更改了为报告选择数据的 SP 来进行此更新，但结果是，如果报告存在问题，或者发送电子邮件时出现问题（例如，网络已关闭），则系统认为报告已发送，但实际上尚未发送。
是的，我们可以（在某些情况下）编写自己的小服务/应用程序来读取数据库，抓取报告，通过电子邮件发送，然后再次更新数据库，但这似乎不应该尽管这很困难。
我在这里寻找任何建议。这一定是人们想要对报告做的最常见的事情之一，但我看不到任何关于解决这个问题的东西，或者我们可以购买来消除缺点的产品，或者我们可以使用的开源项目等等。看起来我们是要么完全滚动我们自己的系统，要么坚持使用一种非常蹩脚的方法来提供这些报告。
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 that 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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
警告：我只能访问 SQL Server 2005 和 SQL Server 2005。 2008 R2 标准版。我没有足够幸运能够访问企业版。
但是，我知道您可以检查
ReportServer数据库的Subscriptions表中的LastStatus和LastRunTime字段（我假设数据驱动订阅仍将订阅数据存储在订阅表中）。这样，您可以创建并安排一个存储过程来检查订阅的状态和上次发送时间，如果需要再次发送，您可以将订阅的 GUID 传递给 sp_start_job< /code> 存储过程，它将正常发送订阅。您可能需要调整订阅，以便它不会自行发送 - 仅当从计划的存储过程触发时。
这是我过去使用过的东西，但是一旦你拥有数百个订阅，管理起来就很麻烦。
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
LastStatusandLastRunTimefields in theSubscriptionstable of theReportServerdatabase (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_jobstored 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.
如果您想查看某个人是否收到了特定报告，您可以查看 msdb。这可能会给您一个想法，它关闭了系统视图之一，但还有与附件以及电子邮件是否处于未发送状态相关的其他系统视图。这是如果您正在使用数据库邮件..
克里斯
`
使用 [msdb]
GO
选择
收件人
,复制收件人
,blind_copy_recipients
，主题
，身体
,文件_附件
,发送状态
,convert(varchar ,sent_date,109) 为
来自 dbo.sysmail_sentitems 的 Date_Sent
`
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.