SSRS 2016 Data Driven Subscription is generating one email for each row in report

jbirdme 96 Reputation points
2020-11-19T14:24:01.453+00:00

I have a SSRS 2016 data driven subscription. When there is no data, no email goes out, but when there is data, it sends a duplicate email for the number of rows in the report. Is there an easy way to limit it to one email per time it is run if there are >=1 rows? Thanks,

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,806 questions

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 33,126 Reputation points
    2020-11-20T05:44:44.097+00:00

    Hi @jbirdme ,

    it sends a duplicate email for the number of rows in the report

    Do you mean that the users will get three same e-mails if the report have three rows?

    You said that if there is no data in the report, no e-mail goes out, did you set this with Job steps?

    Please also have a check of your the Schedule to see if the data-driven is set to hours.

    I did some test if there are >=1 rows, the users only get one e-mail.

    Regards，
    Zoe

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    What can I do if my transaction log is full?--- Hot issues November

    How to convert Profiler trace into a SQL Server table -- Hot issues November

  2. jbirdme 96 Reputation points
    2020-11-25T15:31:25.127+00:00

    It is set to day(s) not hours. It is still generating a separate email for each row in the report.

    I set the report via dataset query, as follows:
    EXEC [dbo].[DomesticViolenceQuestionYesEmailed]
    SELECT iif(@@rowcount >0, '<List of recipients>','<your email>') [TO]

    I have only one person in the list of recipients

    Thanks, Jay

    0 comments
  3. jbirdme 96 Reputation points
    2020-11-25T15:35:07.18+00:00

    I just tested running it by hours and it generated two emails again :(

    0 comments
  4. KatKar 0 Reputation points
    2024-04-19T13:26:03.8566667+00:00

    I had the same issue and I just fixed it. The subscription query is only for the subscription, not for the report itself. The report's query was included when you created the report. So the data driven subscription is designed to send out the same number of emails as the rows that the subscription query returns. Change the query to:

    Select top (1) from table

    and you will get one email with all the data.

    0 comments