Programatically change the time of a schedule

Question

Post reply

Programatically change the time of a schedule

Guy Bloch

SSC Enthusiast

Points: 126
More actions
July 6, 2018 at 2:17 pm

#365917

Hello All,
We are using ssrs 2016..
We have approx 20 different schedules.
The Daily one for example runs about 90+ reports.
I would like to be able to change the scheduled runtime programatically based on when the data for the reports is all in place.
For example: Daily schedule normally set for 5:00 am. if data completes early/or held up. (I can test for a flag from a sql job) and update the run time to 15 minutes later.
anyone have any ideas.
thanks in advance
tx-g

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

Are you using enterprise, or standard?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Guy Bloch SSC Enthusiast Points: 126 More actions · Answer 2

Hi there the is for getting back

Enterprise edition

Txg

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Guy Bloch - Friday, July 6, 2018 2:17 PM
Hello All,
We are using ssrs 2016..
We have approx 20 different schedules.
The Daily one for example runs about 90+ reports.
I would like to be able to change the scheduled runtime programatically based on when the data for the reports is all in place.
For example: Daily schedule normally set for 5:00 am. if data completes early/or held up. (I can test for a flag from a sql job) and update the run time to 15 minutes later.
anyone have any ideas.
thanks in advance
tx-g

All is revealed at the following article.

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-schedule-transact-sql?view=sql-server-2017

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Thom A SSC Guru Points: 99130 More actions · Answer 4

Jeff Moden - Friday, July 6, 2018 7:09 PM
Guy Bloch - Friday, July 6, 2018 2:17 PM
Hello All,
We are using ssrs 2016..
We have approx 20 different schedules.
The Daily one for example runs about 90+ reports.
I would like to be able to change the scheduled runtime programatically based on when the data for the reports is all in place.
For example: Daily schedule normally set for 5:00 am. if data completes early/or held up. (I can test for a flag from a sql job) and update the run time to 15 minutes later.
anyone have any ideas.
thanks in advance
tx-g
All is revealed at the following article.
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-schedule-transact-sql?view=sql-server-2017

Actually, that makes more sense than what I had in mind. 🙂

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 5

Guy Bloch - Friday, July 6, 2018 2:17 PM
Hello All,
We are using ssrs 2016..
We have approx 20 different schedules.
The Daily one for example runs about 90+ reports.
I would like to be able to change the scheduled runtime programatically based on when the data for the reports is all in place.
For example: Daily schedule normally set for 5:00 am. if data completes early/or held up. (I can test for a flag from a sql job) and update the run time to 15 minutes later.
anyone have any ideas.
thanks in advance
tx-g

How about using a hierarchical model where the schedules are formed by ordering the jobs by dependencies?
😎

I've done this by using a simple table schema and a control job that fires frequently, the role of the control job is to check which jobs to run and if the prerequisite jobs have completed sucessfully. Relatively simple and works like a charm.

If you need more assistance on this, feel free to ping back and I'll try to dig out the original source 😉

Guy Bloch SSC Enthusiast Points: 126 More actions · Answer 6

Thank you all for your responses

Will try Jeff's option on Monday

Txg

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

Thom A - Saturday, July 7, 2018 4:30 AM
Jeff Moden - Friday, July 6, 2018 7:09 PM
Guy Bloch - Friday, July 6, 2018 2:17 PM
Hello All,
We are using ssrs 2016..
We have approx 20 different schedules.
The Daily one for example runs about 90+ reports.
I would like to be able to change the scheduled runtime programatically based on when the data for the reports is all in place.
For example: Daily schedule normally set for 5:00 am. if data completes early/or held up. (I can test for a flag from a sql job) and update the run time to 15 minutes later.
anyone have any ideas.
thanks in advance
tx-g
All is revealed at the following article.
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-schedule-transact-sql?view=sql-server-2017
Actually, that makes more sense than what I had in mind. 🙂

Thanks Thom but what would make even more sense is to NOT have a schedule for the reports at all and just call the appropriate proc or job or SSIS package (whatever) when the thing (whatever it is) that collects the data has finished collecting the data. We go through the same problem at work and I just don't understand anyone would try to schedule a reporting job where the data may not be present at the appointed time. Make the report dependent on the successful completion of the data acquisition job, instead. That would also ensure the autonomous production of the reports without being dependent on each other due to a common schedule.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jonny.wallman SSC Enthusiast Points: 114 More actions · Answer 8

Or you can create a job without a schedule and call it when the jobs is done with:
exec dbo.sp_start_job @job_id = 'xxxxx'

xxxx = jobid
Run this to find the job_id:

SELECT Job_ID FROM [ReportServerNew].[dbo].[Subscriptions] s Cross Apply(SELECT ScheduleID FROM [dbo].[ReportSchedule] rs Where rs.SubscriptionID = s.[SubscriptionID]) ReportS Cross Apply(Select Job_ID from msdb.dbo.sysjobs sj where cast(sj.Name as varchar(250)) = cast(Reports.ScheduleID as varchar(250)) ) sj Where description = 'XXXX'

XXXX = Description of your Subscription in SSRS

Sue_H SSC Guru Points: 90860 More actions · Answer 9

Guy Bloch - Friday, July 6, 2018 2:17 PM
Hello All,
We are using ssrs 2016..
We have approx 20 different schedules.
The Daily one for example runs about 90+ reports.
I would like to be able to change the scheduled runtime programatically based on when the data for the reports is all in place.
For example: Daily schedule normally set for 5:00 am. if data completes early/or held up. (I can test for a flag from a sql job) and update the run time to 15 minutes later.
anyone have any ideas.
thanks in advance
tx-g

Are these different subscriptions or different jobs that generate reports? Or are they both? It seems that would make quite a difference in terms of how it's addressed.

Sue