Jaa

sp_update_schedule (Transact-SQL)

  • Artikkeli

Changes the settings for a SQL Server Agent schedule.

Syntax

sp_update_schedule 
    {   [ @schedule_id = ] schedule_id 
      | [ @name = ] 'schedule_name' }
    [ , [ @new_name = ] new_name ]
    [ , [ @enabled = ] enabled ]
    [ , [ @freq_type = ] freq_type ]
    [ , [ @freq_interval = ] freq_interval ] 
    [ , [ @freq_subday_type = ] freq_subday_type ] 
    [ , [ @freq_subday_interval = ] freq_subday_interval ] 
    [ , [ @freq_relative_interval = ] freq_relative_interval ] 
    [ , [ @freq_recurrence_factor = ] freq_recurrence_factor ] 
    [ , [ @active_start_date = ] active_start_date ] 
    [ , [ @active_end_date = ] active_end_date ] 
    [ , [ @active_start_time = ] active_start_time ] 
    [ , [ @active_end_time = ] active_end_time ] 
    [ , [ @owner_login_name = ] 'owner_login_name' ]
    [ , [ @automatic_post =] automatic_post ]

Arguments

  • [ @schedule_id = ] schedule_id
    The identifier of the schedule to modify. schedule_id is int, with no default. Either schedule_id or schedule_name must be specified.
  • [ @name = ] 'schedule_name'
    The name of the schedule to modify. schedule_nameis sysname, with no default. Either schedule_id or schedule_name must be specified.
  • [ @new_name= ] new_name
    The new name for the schedule. new_name is sysname, with a default of NULL. When new_name is NULL, the name of the schedule is unchanged.
  • [ @enabled = ] enabled
    Indicates the current status of the schedule. enabledis tinyint, with a default of 1 (enabled). If 0, the schedule is not enabled. When the schedule is not enabled, no jobs will run on this schedule.

  • [ @freq_type = ] freq_type
    A value indicating when a job is to be executed. freq_typeis int, with a default of 0, and can be one of these values.

    Value Description

    1

    Once

    4

    Daily

    8

    Weekly

    16

    Monthly

    32

    Monthly, relative to freq interval

    64

    Run when SQLServerAgent service starts

    128

    Run when the computer is idle

  • [ @freq_interval = ] freq_interval
    The days that a job is executed. freq_interval is int, with a default of 0, and depends on the value of freq_type.

    Value of freq_type Effect on freq_interval

    1 (once)

    freq_interval is unused.

    4 (daily)

    Every freq_interval days.

    8 (weekly)

    freq_interval is one or more of the following (combined with an OR logical operator):

    1 = Sunday

    2 = Monday

    4 = Tuesday

    8 = Wednesday

    16 = Thursday

    32 = Friday

    64 = Saturday

    16 (monthly)

    On the freq_interval day of the month.

    32 (monthly relative)

    freq_interval is one of the following:

    1 = Sunday

    2 = Monday

    3 = Tuesday

    4 = Wednesday

    5 = Thursday

    6 = Friday

    7 = Saturday

    8 = Day

    9 = Weekday

    10 = Weekend day

    64 (when SQLServerAgent service starts)

    freq_interval is unused.

    128

    freq_interval is unused.

  • [ @freq_subday_type = ] freq_subday_type
    Specifies the units for freq_subday_interval. freq_subday_typeis int, with a default of 0, and can be one of these values.

    Value Description (unit)

    0x1

    At the specified time

    0x2

    Seconds

    0x4

    Minutes

    0x8

    Hours
  • [ @freq_subday_interval = ] freq_subday_interval
    The number of freq_subday_type periods to occur between each execution of a job. freq_subday_intervalis int, with a default of 0.

  • [ @freq_relative_interval = ] freq_relative_interval
    A job's occurrence of freq_interval in each month, if freq_interval is 32 (monthly relative). freq_relative_intervalis int, with a default of 0, and can be one of these values.

    Value Description (unit)

    1

    First

    2

    Second

    4

    Third

    8

    Fourth

    16

    Last
  • [ @freq_recurrence_factor = ] freq_recurrence_factor
    The number of weeks or months between the scheduled execution of a job. freq_recurrence_factor is used only if freq_type is 8, 16, or 32. freq_recurrence_factoris int, with a default of 0.

  • [ @active_start_date = ] active_start_date
    The date on which execution of a job can begin. active_start_dateis int, with a default of NULL, which indicates today's date. The date is formatted as YYYYMMDD. If active_start_date is not NULL, the date must be greater than or equal to 19900101.

    After you create the schedule, review the start date and confirm that it is the correct date. For more information, see the section "Scheduling Start Date" in CCreating Schedules.

  • [ @active_end_date = ] active_end_date
    The date on which execution of a job can stop. active_end_dateis int, with a default of 99991231, which indicates December 31, 9999. Formatted as YYYYMMDD.
  • [ @active_start_time = ] active_start_time
    The time on any day between active_start_date and active_end_date to begin execution of a job. active_start_timeis int, with a default of 000000, which indicates 12:00:00 A.M. on a 24-hour clock, and must be entered using the form HHMMSS.
  • [ @active_end_time = ] active_end_time
    The time on any day between active_start_date and active_end_date to end execution of a job. active_end_timeis int, with a default of 235959, which indicates 11:59:59 P.M. on a 24-hour clock, and must be entered using the form HHMMSS.
  • [ @owner_login_name= ] 'owner_login_name']
    The name of the server principal that owns the schedule. owner_login_name is sysname, with a default of NULL, which indicates that the schedule is owned by the creator.
  • [ @automatic_post =] automatic_post
    Reserved.

Return Code Values

0 (success) or 1 (failure)

Remarks

All jobs that use the schedule immediately use the new settings. However, changing a schedule does not stop jobs that are currently running.

Permissions

By default, members of the sysadmin fixed server role can execute this stored procedure. Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb database:

  • SQLAgentUserRole
  • SQLAgentReaderRole
  • SQLAgentOperatorRole

For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles.

Only members of sysadmin can modify a schedule owned by another user.

Examples

The following example changes the enabled status of the NightlyJobs schedule to 0 and sets the owner to terrid.

USE msdb ;
GO

EXEC dbo.sp_update_schedule
    @name = 'NightlyJobs',
    @enabled = 0,
    @owner_login_name = 'terrid' ;
GO

See Also

Reference

sp_add_schedule (Transact-SQL)
sp_delete_schedule (Transact-SQL)
sp_help_schedule (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 November 2008
Changed content:
  • Added information about @active_start_date.