none
SSRS Subscription has stopped sending Emails RRS feed

  • Question

  • So we are being told NOTHING has changed on our SMTP Server. NOTHING has changed on our SQL Server. Yet our Report Subscription that was running fine last week all of a sudden is failing to send an Email. Now it is a rather robust report. 

    Eureka! I think that's it. The last time this subscription ran, it attached and successfully emailed a 25MB Excel spreadsheet. The whole idea behind the Subscription is to run after hours because of the volume. And the 25MB is the Email limitation. So we are thinking that even though the Report looks successful on the Report Manager Portal..."Done: 1 processed of 1 total; 0 errors"...when it goes to Email, the SMTP Email Server says "No No No...can't do it...too big"

    So what are my options? They're not going to budge on the Email Attachment limitation...or should they...what's normal SOP on something like that?

    Can I somehow turn this into a SSIS Package...run the SSRS Report...and render the result set to an Excel spreadsheet somewhere on a network shared drive...

    Thanks for your review and am hopeful for a reply.

    Wednesday, September 18, 2019 8:33 PM

All replies

  • Hi ITBobbyP,

    You could add filter clause in the sql query to split result into two or more smaller dataset. (DS1, DS2, DS3) Then make reports out of there datasets and create separately subscriptions for each of them, send smaller emails.

    Not sure what you network shared drive refers to, but you could set subscription of report to shared folders, this could be one solution if the viewers are all in you domain. File Share Delivery in Reporting Services.



    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 19, 2019 2:54 AM
  • So we are being told NOTHING has changed on our SMTP Server. NOTHING has changed on our SQL Server. Yet our Report Subscription that was running fine last week all of a sudden is failing to send an Email. Now it is a rather robust report. 

    Eureka! I think that's it. The last time this subscription ran, it attached and successfully emailed a 25MB Excel spreadsheet. The whole idea behind the Subscription is to run after hours because of the volume. And the 25MB is the Email limitation. So we are thinking that even though the Report looks successful on the Report Manager Portal..."Done: 1 processed of 1 total; 0 errors"...when it goes to Email, the SMTP Email Server says "No No No...can't do it...too big"

    So what are my options? They're not going to budge on the Email Attachment limitation...or should they...what's normal SOP on something like that?

    Can I somehow turn this into a SSIS Package...run the SSRS Report...and render the result set to an Excel spreadsheet somewhere on a network shared drive...

    Thanks for your review and am hopeful for a reply.

    You can confirm whether this is the case by looking into C:\inetpub\mailroot\Badmail folder in your server where SMTP is configured

    It will have a .BAD file with email contents and with error message like below

    Diagnostic-Code: smtp;450 5.2.3 Msg Size greater than allowed by Remote Host

    To fix this issue a good option is to convert data to multiple files based on any criteria (like say date ie sent monthly/weekly etc report) and then set subscription for it


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, September 19, 2019 5:57 AM