Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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

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

 

 

 

1 ACCEPTED SOLUTION
Vosaxalo90
Frequent Visitor

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.

View solution in original post

7 REPLIES 7
v-alipat
Employee
Employee

Hi all, 

@Anonymous , @Vosaxalo90 , 

 

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 

Vosaxalo90
Frequent Visitor

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.

Anonymous
Not applicable

Thanks so much @Vosaxalo90 for this answer it also works for us!

 

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

Vosaxalo90
Frequent Visitor

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. 

lbendlin
Super User
Super User

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

Anonymous
Not applicable
Anonymous
Not applicable

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.