Changing Schedules

Question

Changing Schedules

Steve Jones - SSC Editor

SSC Guru

Points: 734418
More actions
August 19, 2015 at 8:01 pm

#75724

Comments posted to this topic are about the item Changing Schedules
August 20, 2015 at 1:39 am

This was removed by the editor as SPAM

Viewing 14 posts - 1 through 13 (of 13 total)

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

Hany Helmy SSChampion Points: 13921 More actions · Answer 1

I got it right, but the explanation is wrong; as "the freq_subday_type needs to be updated to hours with the value of 8 NOT 4", beside the original value for that paramater was 1 not 4!

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 2

Good question. I don't think many people are aware of this alternative method to change schedules. (And it can be handy to know it exists in edge cases where you need to programmatically change the schedule of a job).

When replying to the question, I did not look at the "(from XX)" notes after each answer, as sp_update_jobschedule only takes new values and does not request specifying the old values as well. But in all honesty, I don't think they are correct. "From 4" on subday_type suggests that the job was formerly run every so many minutes; I think that the setting was 1 ("at the specified time"). And the freq_subday_interval could have been anything because subday_type 1 ignores it; it probably was NULL (its default value).

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

Nice question. I have to admit I prefer the UI 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

michal.lisinski SSCertifiable Points: 6933 More actions · Answer 4

Hi

Good question, but a bit easy to make mistake.

Br.

Mike

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

The UI is simpler, yes, but it's helpful to know the command if, for example, you're doing the same change on a lot of servers, or you're scripting a change that has to be promoted (and tested) through several environments.

John

patricklambin SSCrazy Eights Points: 9994 More actions · Answer 6

I have been lucky when I have done my choices as I have not used SQL Server Agent since 2 years.But after 5 minutes , I have done my choice as I was remembering only for these 2 parameters.

Anyway , after having validated my choices , I hurried on the provided link and I got more explanations.

I think I will have to restart to use SQL Server Agent , maybe next week.

Now , I am too busy after my upgrade from Windoxs 8.1 upto Winows 10 ( 7 hours )

Anyway , an useful question as I had to dig in the depth of my own memory.

So , thank you very much.

djj SSCoach Points: 18831 More actions · Answer 7

Interesting. Too bad it is the wrong command. (Not really)

From BOL

Changes the schedule settings for the specified job.
sp_update_jobschedule is provided for backward compatibility only.
Important:
For more information about syntax used in earlier versions of Microsoft SQL Server, see the Transact-SQL Reference for Microsoft SQL Server 2000.
Remarks
Job schedules can now be managed independently of jobs. To update a schedule, use sp_update_schedule.

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 8

Koen Verbeeck (8/20/2015)
Nice question. I have to admit I prefer the UI 🙂

+1

Recently we have changed the job schedule to run for every 1 hour, so it's been little easy.

Thanks for the question steve

g.britton SSChampion Points: 13863 More actions · Answer 9

Using her phone, Sarah can easily run a query through a command line to change the job

How does she do that? What kind of phone (Android, IOS, something else? or does it matter) and what app?

Gerald Britton, Pluralsight courses

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 10

g.britton (8/20/2015)
Using her phone, Sarah can easily run a query through a command line to change the job
How does she do that? What kind of phone (Android, IOS, something else? or does it matter) and what app?

Lots of RDP apps on all platforms. Montioring systems allow scheduling and running of commands as well, with parameters.

TomThomson SSC Guru Points: 104773 More actions · Answer 11

Hugo Kornelis (8/20/2015)
Good question. I don't think many people are aware of this alternative method to change schedules. (And it can be handy to know it exists in edge cases where you need to programmatically change the schedule of a job).
When replying to the question, I did not look at the "(from XX)" notes after each answer, as sp_update_jobschedule only takes new values and does not request specifying the old values as well. But in all honesty, I don't think they are correct. "From 4" on subday_type suggests that the job was formerly run every so many minutes; I think that the setting was 1 ("at the specified time"). And the freq_subday_interval could have been anything because subday_type 1 ignores it; it probably was NULL (its default value).

+1

Those "from" values are a silly distraction. Putting 0 in freq_subday_interval is a very silly thing to do (if it's actiually possible to do it - I've never tried so I don't know, but I believe it ought not to be possible).

Tom

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 12

Hi Steve

After we tick the answers, it will be good if we still can see the options so that while we see the correct answer, we will be able to see the options provided.

Can you please add the functionality?

Thanks.