Post reply

rescheduling sql job

  • January 31, 2005 at 6:02 am

    #109305

    Hi,

    I need to reschedule a job. SQL Books suggest using sp_update_jobschedule to update the job.

    But the stored proc does not exist on my system and returns:

    Could not find stored procedure 'sp_update_jobschedule'

    Where can i find sp_update_jobschedule or its equivalent??

    regards,

    Hiren

  • January 31, 2005 at 8:38 am

    #539756

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ua-uz_9het.asp

    It's in the msdb database. Have you looked there?

  • January 31, 2005 at 12:50 pm

    #539788

    As Mark said, the job related procedures are in msdb, and if you execute them from any other database, the procedure name must be qualified(msdb..sp_update_jobschedule).  However, if you are just wanting to do a one time change to a job schedule, in other words, this isn't procedural where you are writing a procedure to change the schedule, it would be much, much easier just to do it from Enterprise Manager.  Expand the server in the left hand pane, expand Management, expand SQL Server Agent, select jobs, find the job you wish to change in the right hand pane,  right click on the job and select properties, select the Schedules tab, select the schedule you wish to change and click the edit button.  If you are changing a recurring schedule, click the Change... button and make your required changes there.  It sounds like a lot of steps, but nearly all of it is navigating through Enterprise Manager to get to the job.  Its really pretty simple.

    Steve

  • January 31, 2005 at 9:06 pm

    #539846

    Thanks Mark and Steve. Got the proc.

    I was looking for it in the master db.

    And since i require to reschedule the job on an event in my application, i used the sp and not the Enterprise manager.

    regards,

    Hiren

     

  • February 1, 2005 at 12:08 am

    #539861

    keep in mind the security restrictions for job-alters. Don't run your app with sa-permissions!

    declare @NewDate as int

    declare @NewTime as integer

    declare @dtNewTime as datetime

    -- wait for 45 minutes

    set @dtNewTime = dateadd(mi,45,getdate())

    select @NewDate = convert(integer,(replace(convert(char(10),@dtNewTime,121),'-','')))

    , @NewTime = convert(integer,substring(replace(convert(char(25),@dtNewTime,121),':',''),12,4)+'00')

    EXECUTE msdb.dbo.sp_update_jobschedule @job_name = N'yourjob', @name = N'your schedule', @enabled = 1, @freq_type = 1, @active_start_date = @Newdate, @active_start_time = @Newtime

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

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

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