SQL Server 2008R2 Reporting Services: Data Driven Subscription

Question

4.00/5 (1 vote)

See more:

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 ?

Posted 3-Sep-12 5:14am

Luk Vandevyvere

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Luk Vandevyvere · Accepted Answer · 2012-09-03T21:01:00

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...