cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cnschulz
Helper IV
Helper IV

SQL to Alter Refresh Schedules

Gday, 

 

On our DEV server, Id like to restrict users from creating long term refresh schedules to reduce the data impact. My plan is to use the "stop this schedule on" mechanism and stop all active schedules after one week. I have found 3 (!!) tables I need to edit on order for this to work correctly:

 

  • ReportServer.dbo.Schedule
  • ReportServer.dbo.Subscriptions (Stored in XML!!!!)
  • msdb.dbo.sysschedules

Each table has an "end date" but each is in a different format. While it is possible to do what I want in a scheduled SQL task, Im wondering if anyone else has considered this or can offer an alternative solution.

 

Cheers

 

1 REPLY 1
d_gosbell
Super User II
Super User II

It's not advisable to directly edit the tables behind the PBIRS instance. A better approach is to use the supported APIs to alter the subscriptions. You can use the PowerShell Reporting Services Tools (see https://github.com/Microsoft/ReportingServicesTools). It has get-RSSubscription/set-RSSubscription cmdlets that would let you find and alter subsciptions.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps