SQL Server 2008R2 Reporting Services:数据驱动订阅 [英] SQL Server 2008R2 Reporting Services: Data Driven Subscription

问题描述

我是Reporting Services的新手，我希望能够在创建记录时自动向客户发送电子邮件.
我已经能够配置报告服务器并发送包含报告的电子邮件:到目前为止一切顺利.

订阅后面的查询基于表中的某个标志"ReportSendByEmail".只能通过电子邮件发送该标志设置为0的记录的报告.
因此，我想通过添加发送报告后将位值更新为1的步骤来适应SQL Server作业.

奇怪的是，添加该步骤后，不再发送报告.
-步骤1(发送报告)配置为成功进行下一步"
-步骤2(更新标志)配置为退出作业报告成功"
但是，位标记已更新为1.

检查工作历史:没有提及任何错误，一切都应该没问题.
但是邮箱保持为空.仅当删除步骤2时，邮件才会回来(所有电子邮件地址均设置为我的本地地址).

知道这可能是什么原因吗?
也许是安全问题:
-第一步由用户"NT AUTHORITY \ NETWORK SERVICE"处理
-第二步由用户"sa"处理
两个用户都可以完全访问包含必需数据的数据库.

有任何想法吗?

Hi,

I''m new to Reporting Services and I want to be able to automatically send an email to a customer when a record is created.
I''ve been able to configure the report server and send the email containing the report: so far so good.

The query behind the subscription is based on a certain flag ''ReportSendByEmail'' in a table. Only reports with records with this flag set to 0 should be sent by email.
Therefore, I want to adapt the SQL Server Job by adding a step which updates the bit-value to 1 after the report has been sent.

Strangely enough, after adding that step, the report isn''t sent anymore.
- Step 1 (sending the report) is configured to ''go the next step on success''
- Step 2 (updating the flag) is configured to ''Quit the job reporting successs''
The bit-flag however is updated to 1.

Checking the job-history: No errors are mentioned, everything should be ok.
But the mailbox stays empty. Only when removing step 2, the mails are coming back in (all email-addresses are set to my local address).

Any idea what may be the cause of this ?
Maybe it''s a security issue:
- The first step is processed by user ''NT AUTHORITY\NETWORK SERVICE''
- The second step is processed by user ''sa''
Both users have full access to the database containing the necessary data.

Any ideas ?

推荐答案

再见，

由于尚未有人提出解决方案，因此我使用了以下解决方法:

1.我使用查询创建了一个存储过程，以检索应通过电子邮件发送的所有记录.
2.存储过程具有一个可选参数"@ReportSentByEmail"(位，默认为NULL):如果此参数为1，则在SELECT
后执行UPDATA命令. 3.我更改了预订，因此使用了上面的存储过程，其中parameter-value = 1

每次执行订阅作业时，都会从数据库中选择要通过电子邮件发送的数据，并将相同的数据更新为ReportSentByEmail = True.这样，数据仅发送一次.

我知道这不是做这样的事情的正确方法(因为我不检查电子邮件是否已实际发送)，但是现在，我可以接受它.

还有其他想法还是很感激的...

Hi again,

Since no one came up with a solution yet, I''ve used the following workaround:

1. I created a stored procedure with the query to retrieve all records which should be sent by email.
2. The stored procedure has an optional parameter ''@ReportSentByEmail'' (bit, default NULL): In case this parameter is 1, an UPDATA-command is executed after the SELECT
3. I changed the subscription so the above stored procedure is used, with parameter-value = 1

Every time the subscription-job is executed, the data to be send by email is selected from the database, and that same data is updated to ReportSentByEmail = True. This way, the data is only sent once.

I know this is not the right way to do stuff like this (since I do not check if the email has actually been sent) but for now, I can live with it.

Any other ideas are still very appreciated...

这篇关于SQL Server 2008R2 Reporting Services:数据驱动订阅的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！