SSRS Subscrptions - Reschedule subscription run times via report server.

Hello

We are looking for the best way to reschedule our report subscriptions on the report server.   With hundreds of subscriptions its not practical to go thru each one on the web site.  We can query our subscription data on the report server and I see the Schedule table.  However its not clear how to easily change the start time for the subscriptions.

Any help or direction appreciated.
Thanks!
Mitch Small
Mitch SmallSQL Server DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
--So this query should give you all associated SSRS reports/SQL jobs and their schedules.
--All you will need is to update next_run_date/next_run_time for each of the listed schedules in the msdb.dbo.sysschedules however...
--PLEASE make sure you back up the table or even better the MSDB database AND
--PLEASE perform the changes in a DEV/QA test environment first

-- Get list of reportnames and associated SQL jobs
select      distinct
      'ReportName' = c.name,
      'SQLJobName' = j.name,
      'JobScheduleId'= js.schedule_id,
      'ScheduleName'=sc.name
from ReportServer.dbo.Subscriptions s
      inner join ReportServer.dbo.Catalog c on c.ItemID = s.Report_OID
      inner join ReportServer.dbo.Users us on us.UserID = s.OwnerId
      inner join msdb.dbo.sysjobs j on j.job_id = (select job_id from msdb.dbo.sysjobsteps where command like '%'+CONVERT(nvarchar(128),s.Report_OID)+'%')
      inner join msdb.dbo.sysjobschedules js on j.job_id=js.job_id
      inner join msdb.dbo.sysschedules sc on js.schedule_id=sc.schedule_id

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.