Allows you to modify any of the fields in a scheduled job, a job, or a schedule.
sp_sjobmodify @name=’...’, @option=’...’
The name or ID of the scheduled job, job, or schedule that is to be modified.
A comma-separated list of the field names to modify and the new values:
sjname – a new name for the scheduled job that must be unique. A new ID may not be supplied.
enable – 1 enables the scheduled job, 0 disables it.
sjproperties – the properties of the scheduled job.
sjowner – the owner of the scheduled job. Caller must have js_admin_role to change the owner.
server – the server where the scheduled job should run.
timeout – a timeout value specified for the scheduled job and server, in minutes.
locale – the locale to be used by the client connection when the scheduled job runs.
jname – a new name for the job; this must be unique. A new ID may not be supplied.
jdesc – comments describing the job.
jcmd – SQL text used for simple jobs when it is convenient to supply the text directly. Replaces all existing SQL text.
jproperties – the properties of the job, the schedule, or scheduled job: jproperties, sproperties, or sjproperties, respectively. These properties are:
jproperties (job properties)
multi_task
run_as_owner
no_job_header
no_sql_batching
no_conn_redirection
shared
sjproperties (scheduled job properties)
shared_run
only_at_starttime
disable_on_faillure
delete_on_completion
no_output_log
shared (schedule property only
jowner – the owner of the job. Caller must have js_admin_role to change the owner.
default_timeout – a timeout value for the job, in minutes.
sname – a new name for the schedule; this must be unique. A new ID may not be supplied.
sdesc – comments about the schedule.
sowner – must have js_admin_role to change the owner.
sproperties – schedule properties.
reset – “true” resets the timing information in the schedule. It clears the following: repeat, units, startdate, starttime, enddate, endtime, days, and dates.
repeats – the interval at which the schedule repeats. NULL or 0 means it does not repeat. The value is a number followed by:
day or d
days or dd
hour or h
hours or hh
minute or m
minutes or mm
startdate – the schedule becomes active on this date. The time part is ignored.
enddate – the schedule becomes inactive. The time part is ignored.
starttime – the time of day when an active schedule begins to operate. The date part is ignored. If starttime is not specified, it defaults to midnight (00:00).
endtime – the time of day when an active schedule ceases to operate. The date part is ignored. If endtime is not specified, it defaults to midnight (00:00).
days – a colon-separated list of days. Full or abbreviated names in the locale of the server may be used.
dates – a colon-separated list of dates in a month.
Returns 0 on success or an error code.
The following example modifies the scheduled job “svr1_clean_stats,” updating the schedule to repeat every two hours between 10:00 and 16:00. The days or dates on which the scheduled job operates are unchanged.
sp_sjobmodify @name=’svr1_clean_stats’, @option=’repeats=2hours,starttime=10:00,endtime=16:00’
The following example modifies the job “backup_db5,” updating the default timeout to 120 minutes:
sp_sjobmodify @name=’jname=backup_db5’, @option=’default_timeout=120’
The following example modifies the job named “orders_picked_report,” removing the shared property and changing the owner to mary. The caller requires js_admin_role to change the job owner.
sp_sjobmodify @name=’jname=orders_picked_report’, @option=’jproperties=shared:false,owner=mary’
By default, the @name argument is the name or ID of a scheduled job. To specify the name or ID of a job or a schedule, the @name argument is prefixed with jname or sname.
Following is an example of using @jname:
@name= ‘jname=run_update_stats’
Following is an example of using @sname:
@name= ‘sname=daily_schedule’
