Adjusted SQL Agent job schedule for SSRS report keeps reverting back to original timing!

Question

Adjusted SQL Agent job schedule for SSRS report keeps reverting back to original timing!

tacy.highland

Hall of Fame

Points: 3319
More actions
November 7, 2018 at 10:16 am

#378583

I have an SSRS SQL Agent job running for an SSRS report (email subscription) which was set to run every hour in the subscription management GUI. I had to modify the SQL Agent job so it would send every 3 hours between 3am and 9pm instead of every hour all day long. (I've done this same thing on a job for another report in a different environment and it worked for years). For some reason, this job just won't keep the schedule changes. The changes are made and saved, but at some point the job just reverts back to the original every hour schedule. We even created a second schedule record in the Schedule List window, disabling the original hourly schedule, but eventually (don't know when) it deletes both schedules and just creates a new hourly schedule.
I've never seen this before and not sure how I can get it to run when I need it to.
Any ideas out there?
Thanks much!

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

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

Sreekanth B SSCertifiable Points: 6145 More actions · Answer 1

You have to change the subscription timings/Schedules from report manager, those changes will be reflected on the backend job(s). I wouldn't fiddle with underlying SQL Agent jobs for managing report subscriptions..

tacy.highland Hall of Fame Points: 3319 More actions · Answer 2

The GUI doesn't allow me to modify hourly settings beyond just sending a report every hour. I've done this to reports/jobs before and have never had any issues ...until this one. Just curious if anyone else has run into this before and maybe has ideas on how to resolve it.

Sreekanth B SSCertifiable Points: 6145 More actions · Answer 3

Never had to do this...So, what happens if you disable the existing schedule(Which got created by your subscription) in the underlying Agent job and create a new custom schedule as per your needs?

Sreekanth B SSCertifiable Points: 6145 More actions · Answer 4

Sreekanth B - Wednesday, November 7, 2018 10:50 AM
Never had to do this...So, what happens if you disable the existing schedule(Which got created by your subscription) in the underlying Agent job and create a new custom schedule as per your needs?

Never mind...you already posted that information in your original question.

tacy.highland Hall of Fame Points: 3319 More actions · Answer 5

Did that.
It just deleted both schedules at some point and created an entirely new hourly schedule (with a new ID).

This is just the oddest behavior.

Sue_H SSC Guru Points: 90860 More actions · Answer 6

tacy.highland - Wednesday, November 7, 2018 10:54 AM
Did that.
It just deleted both schedules at some point and created an entirely new hourly schedule (with a new ID).
This is just the oddest behavior.

Did you check the creation, modification date for the job? It sounds like the job could be being recreated - that what would cause it to go back to use the schedule listed in SSRS.
And is the report being updated, changed in some way when it just starts reverting to the old schedule?

One other thought about the schedules...what happens if you select the hour for the schedule and then set that to every three hours. It's not restricted to just hourly. So you select Hour and then to the right select it to run every three hours.

Sue

tacy.highland Hall of Fame Points: 3319 More actions · Answer 7

The job isn't getting modified by anyone (other than when we try to make the changes we need). The regular hourly schedule works fine for regular hourly functionality, (including making it run every 3 hours), but it won't just run between the specified hours because the schedule keeps reverting back to the original specs.

I may just be stuck with running it every 3 hours all day long. It's just a few extra email every day.

Thanks

Sue_H SSC Guru Points: 90860 More actions · Answer 8

tacy.highland - Wednesday, November 7, 2018 11:42 AM
The job isn't getting modified by anyone (other than when we try to make the changes we need). The regular hourly schedule works fine for regular hourly functionality, (including making it run every 3 hours), but it won't just run between the specified hours because the schedule keeps reverting back to the original specs.
I may just be stuck with running it every 3 hours all day long. It's just a few extra email every day.
Thanks

I was not thinking anyone was changing the jobs but that SSRS was creating a new job. The schedule continually reverting back to the schedule listed in SSRS indicates that this is likely. Similar (but not the same) as when you move reporting services to another server and don't move the subscription jobs. The jobs get created by SSRS

You had mentioned earlier about having to run the jobs every hour - I just mentioned the every three hours as an alternative to hourly.

Sue