Changing Schedules

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719935

    Comments posted to this topic are about the item Changing Schedules

  • Hany Helmy

    SSChampion

    Points: 13488

    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!

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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: 258965

    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

  • This was removed by the editor as SPAM

  • michal.lisinski

    SSCertifiable

    Points: 6933

    Hi

    Good question, but a bit easy to make mistake.

    Br.

    Mike

  • John Mitchell-245523

    SSC Guru

    Points: 148761

    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: 9964

    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: 18738

    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

    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: 13689

    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, MCSE-DP, MVPToronto PASS Chapter[/url]

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719935

    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

    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: 33450

    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.

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

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