topic Re: Update from January 2023 to September 2023, now data-driven subscriptions not emailing! in Report Server

Update from January 2023 to September 2023, now data-driven subscriptions not emailing!

Anonymous — Thu, 26 Oct 2023 15:10:45 GMT

Hi, We have updated our Power BI Report Server from January 2023 to September 2023, email delivery data-driven subscriptions (pageinated reports) were working, but not now!

After the update, reports displayed properly. I could download and upload report definitions after making changes.

There was an issue with regular email subscriptions not triggering but I found references to permission problems on the MSDB and Master databases for SQL Agent and granted them. Errors like this:

System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'sp_help_category'

after fixing that, then had this one;

System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs'

Permissions granted:

GRANT EXECUTE ON msdb.dbo.sp_add_category TO RSExecRole
GRANT EXECUTE ON msdb.dbo.sp_add_job TO RSExecRole
GRANT EXECUTE ON msdb.dbo.sp_add_jobschedule TO RSExecRole
GRANT EXECUTE ON msdb.dbo.sp_add_jobserver TO RSExecRole
GRANT EXECUTE ON msdb.dbo.sp_add_jobstep TO RSExecRole
GRANT EXECUTE ON msdb.dbo.sp_delete_job TO RSExecRole
GRANT EXECUTE ON msdb.dbo.sp_help_category TO RSExecRole
GRANT EXECUTE ON msdb.dbo.sp_help_job TO RSExecRole
GRANT EXECUTE ON msdb.dbo.sp_help_jobschedule TO RSExecRole
GRANT EXECUTE ON msdb.dbo.sp_verify_job_identifiers TO RSExecRole
GRANT SELECT ON msdb.dbo.syscategories TO RSExecRole
GRANT SELECT ON msdb.dbo.sysjobs TO RSExecRole

use master;
GO

GRANT EXECUTE ON master.dbo.xp_sqlagent_enum_jobs TO [RSExecRole]
GRANT EXECUTE ON master.dbo.xp_sqlagent_is_starting TO [RSExecRole]
GRANT EXECUTE ON master.dbo.xp_sqlagent_notify TO [RSExecRole]
GRANT EXECUTE ON master.dbo.sp_helplogins TO [RSExecRole]

After the permission updates the email subscriptions started working. Test of File share type subscriptions were also successful.

So far I have tested my data-driven subscription, the data source\dataset SELECT - it still works for the user defined. I can't seem to see anything else in SQL Server that may be the problem.

Any ideas where to look next? Should we just rollback the server and try the update again? Completely uninstall and reinstall?

Thanks

Gord

Error log details:

------------------------------------------------

schedule!WindowsService_2!5d0!10/25/2023-14:30:09:: i INFO: Handling event with data: TimeEntered: 10/25/2023 14:30:09, Type: Event, EventType: TimedSubscription, SubscriptionID: 992cbf9a-d7ec-4456-91da-a7087a3c6306.
library!WindowsService_2!5d0!10/25/2023-14:30:09:: i INFO: Schedule 037d0970-8df9-4bc0-9006-54effd424f0c executed at 10/25/2023 14:30:09.
schedule!WindowsService_2!5d0!10/25/2023-14:30:09:: i INFO: Creating Time based subscription notification for subscription: 992cbf9a-d7ec-4456-91da-a7087a3c6306
library!WindowsService_2!5d0!10/25/2023-14:30:09:: i INFO: Schedule 037d0970-8df9-4bc0-9006-54effd424f0c execution completed at 10/25/2023 14:30:09.
schedule!WindowsService_2!de8!10/25/2023-14:30:09:: i INFO: Handling event with data: TimeEntered: 10/25/2023 14:30:09, Type: Event, EventType: DataDrivenSubscription, SubscriptionID: 992CBF9A-D7EC-4456-91DA-A7087A3C6306.
library!WindowsService_2!1100!10/25/2023-14:30:09:: i INFO: Handling data-driven subscription 992cbf9a-d7ec-4456-91da-a7087a3c6306 to report /SubscriptionTesting/SSRSReportTesting, owner: (xxxADxxx)\(xxxUserxxx), delivery extension: Report Server Email.
library!WindowsService_2!1100!10/25/2023-14:30:09:: i INFO: Initializing EnableExecutionLogging to 'True' as specified in Server system properties.
dataextension!WindowsService_2!1100!10/25/2023-14:30:09:: i INFO: Opening SqlConnection with ClientConnectionId f3c89dba76ae4b07a895ccd8d1eae9ba
library!WindowsService_2!694!10/25/2023-14:30:09:: i INFO: Initializing EnableExecutionLogging to 'True' as specified in Server system properties.
notification!WindowsService_2!694!10/25/2023-14:30:09:: i INFO: Handling subscription 992cbf9a-d7ec-4456-91da-a7087a3c6306 to report SSRSReportTesting, owner: (xxxADxxx)\(xxxUserxxx), delivery extension: Report Server Email.
emailextension!WindowsService_2!694!10/25/2023-14:30:09:: e ERROR: Error sending email. Exception: System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.Deliver(Notification notification). Additional Information:
notification!WindowsService_2!694!10/25/2023-14:30:30:: e ERROR: Error occurred processing subscription 992cbf9a-d7ec-4456-91da-a7087a3c6306: Failure sending mail: Object reference not set to an instance of an object.Mail will not be resent.
notification!WindowsService_2!694!10/25/2023-14:30:30:: i INFO: Notification f85c3820-d88a-4b79-92e7-bb9bd16caaed completed. Success: False, Status: Failure sending mail: Object reference not set to an instance of an object.Mail will not be resent., DeliveryExtension: Report Server Email, Report: SSRSReportTesting, Attempt 0

Re: Update from January 2023 to September 2023, now data-driven subscriptions not emailing!

lbendlin — Sat, 28 Oct 2023 00:39:57 GMT

You can raise an issue at https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues . If you have a Pro license you can consider raising a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi

Re: Update from January 2023 to September 2023, now data-driven subscriptions not emailing!

Anonymous — Tue, 31 Oct 2023 13:28:02 GMT

Same issue here. Exactly like you, we have recently upgraded Report Server from January 2023 to September 2023. Now, the previously working data-driven subscritpions doesn't work anymore.

So far no solution or workaround found.

Re: Update from January 2023 to September 2023, now data-driven subscriptions not emailing!

Anonymous — Thu, 02 Nov 2023 14:07:42 GMT

Posted to "Issues"

Data-driven subscriptions not emailing after updat... - Microsoft Fabric Community

Re: Update from January 2023 to September 2023, now data-driven subscriptions not emailing!

Anonymous — Fri, 03 Nov 2023 17:37:07 GMT

"Issues" forum has sent it back to this "Report Server" forum ☹️

Re: Update from January 2023 to September 2023, now data-driven subscriptions not emailing!

Anonymous — Thu, 16 Nov 2023 06:47:36 GMT

In the meantime we have found out that the problem is caused by the fields in the dataset that contain the "NULL" value. For instance, if the field [CC] is used in the data-driven subscription, but you don't have always an email to send to, and you leave NULL in the [CC] column of the dataset, then the email will not work. If you set the [CC] column to empty (delete the NULL), then the email will work again.

Re: Update from January 2023 to September 2023, now data-driven subscriptions not emailing!

Anonymous — Thu, 16 Nov 2023 13:36:52 GMT

Thanks so much Anonymous for this answer it also works for us!

Microsoft - add this to your breaking changes list on this update!

Re: Update from January 2023 to September 2023, now data-driven subscriptions not emailing!

v-alipat — Thu, 16 Nov 2023 15:42:56 GMT

Hi all,

Anonymous , Anonymous ,

Thank you for reporting the issue! 🙏

We found the fix and it will be available with the next releases of PBIRS, as well as SSRS19 and 22.

Unfortunately, at the moment I don't have a precise ETA for the next release or QFE to share.

Until it's done, as a workaround you may edit your datasets to set empty strings instead of NULLs, it will help.

Sorry about the inconvenience.

Thanks,

Alexandra