SSRS Subscription Schedule Updates via SSMS

Lisa Callahan
Lisa Callahan used Ask the Experts™
on
I have written a procedure to update SSRS subscription schedules. The procedure appears to work because the values are updated in ReportServer.dbo.Schedule. However when I view the schedule through SSRS Report Manager, the dates are not updated.

The code I am using is below -

declare 
--@SubscriptionID varchar(100) = 'E65E6D4A-A631-4C17-A8DB-001FB0AD0855'
@Path varchar(max) = '/QTS Internal/RTM/Service Disruption Report'
, @SPLC varchar(max) = '123456'
, @State varchar(max) = 'VA,NC'
, @Country varchar(max) = NULL
, @Carrier varchar(max) = NULL
, @Location varchar(max) = NULL
, @CustomerID varchar(max) = '1'
, @Disruption varchar(max) = 'Hurricane Florence'
, @StartDate datetime = '20180914 04:19:00'
, @EndDate datetime = '20181002'

create table #subs (SubscriptionID varchar(200), [Path] varchar(200))

insert into #subs

select s.SubscriptionID, c.Path

from Catalog c
join subscriptions s on c.ItemID = s.Report_OID
where c.Path = @Path

-----------------------------------------------------update schedule dates

UPDATE ReportServer.dbo.Schedule
SET StartDate = @StartDate, EndDate = @EndDate
from Catalog c
LEFT OUTER JOIN Subscriptions s WITH (NOLOCK) ON s.Report_OID = c.ItemID
LEFT OUTER JOIN ReportServer.dbo.[ReportSchedule] RS WITH (NOLOCK) ON s.[SubscriptionID] = RS.[SubscriptionID] 
LEFT OUTER JOIN ReportServer.dbo.[Schedule] SC WITH (NOLOCK) ON RS.[ScheduleID] = SC.[ScheduleID]
JOIN #subs subs on s.SubscriptionID = subs.SubscriptionID

Open in new window


Does anybody have experience with this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Nakul VachhrajaniTechnical Architect, Capgemini India

Commented:
I do not believe this would work completely. We know that Reporting Services relies not only on the data stored in various tables, but also on components exposed by the operating system (e.g. registries, etc). By only updating the T-SQL part of it, the non-RDBMS items would never receive an update on the changes. Also, in order for the database updates to take effect, you would need to restart the SQL Server Agent service (which may not be possible in all environments).

I would vote to leverage some sort of a PowerShell script to do this. Because the script would leverage native methods, they would be able to take care of both the sides - the database and the services side of things.
References:
Business Intelligence Analyst
Commented:
I am using Shared Schedules as a workaround.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial