This procedure serves several purposes:
When only job information is supplied, it creates a new job.
When only schedule information is supplied, it creates a new schedule.
When job and schedule information is supplied, it either combines an existing job and schedule to create a new scheduled job, or creates a new job and a new schedule, and combines them into a new scheduled job.
By default, the @name argument is the name of a scheduled job. To specify the name of a job or a schedule, the @name argument is prefixed with jname or sname.
sp_sjobcreate @name=’jsname’, @options=’server, jname, jdesc, jcmd, sname, sdesc, repeats, properties, starttime, enddate, endtime, days, dates’
The name of the new job, schedule, or scheduled job.
When you create a name for a job, a schedule, or a scheduled
job, the name must begin with a letter. If you create a name beginning
with a digit, an error occurs.
A list separated by commas of the field names and values you use to create a job, a schedule, or a scheduled job. Values are:
server – the name of the server where the job runs. The default is the local server.
jname – the name of the job, which must be unique.
jdesc – comments describing the job.
jcmd – the SQL text, used for simple jobs when it is easy to provide the text directly.
sname – the name of the schedule, which must be unique.
default_timeout – the maximum amount of time permitted for the execution of a job. This value is used by scheduled jobs if the scheduled job’s timeout property is not set.
sdesc – comments describing the schedule.
timeout – the maximum amount of time a scheduled job can execute. This value supercedes the job's default_timeout value
repeats – the interval at which the schedule repeats. 0 or NULL 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
properties – 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
startdate – the date on which a schedule becomes active.
starttime – the time of day when a scheduled job begins.
enddate – the date on which a schedule becomes inactive.
endtime – the time at which a schedule becomes inactive for the remainder of the day.
days – a list of days separated by colons. Full names or abbreviations in the locale of the server may be used.
dates – a list of dates in a month, from 1 to 31, separated by colons. The number 32 represents the last day of any month.
An error occurs if you try to specify values for both
days and dates.
The job ID for a new job.
The schedule ID for a new schedule.
The scheduled job ID for a new scheduled job.
An error code.
The following example creates a new job called “find_old_logins” that consists of running a stored procedure:
sp_sjobcreate @name=’jname=find_old_logins’ @option=’jcmd=”exec sp_find_old_logins”,jproperties=shared’
The following example creates a new schedule that becomes valid at 1:00 am and repeats every day:
sp_sjobcreate @name=’sname=daily 01:00am’, @option=’repeats=1day,starttime=01:00am, endtime=02:00am’
The following example creates a new scheduled job to run on server “dev1” using the existing job “find_old_logins” and schedule called “daily 01:00am”:
sp_sjobcreate @name=’dev1_old_logins’, @option=’server=dev1,jname=find_old_logins, sname=daily 01:00am’
The following example creates a new schedule called “evening_sales_report,” using new job called “load_sales_data,” and a new schedule that runs every Monday, Wednesday and Friday at 23:00. The schedule is given a default name based upon the ID value in the schedule table.
sp_sjobcreate @name=’evening_sales_report’ @option=’server=reports, jname=load_sales_data, jcmd=exec sp_new_sales_data, starttime=23:00,endtime=23:00,days=Monday:Wednesday:Friday’
A schedule using both a days value and a dates value is not allowed. However, separate schedules using days and dates can be bound to the same job.
A schedule using a repeats value of more than one day is not valid with days or dates values.
A schedule using a repeats value equal to one day is not valid with days or dates values. By default all days are valid when repeats equals one day.
A schedule using a repeats value of less than one day and a days value of NULL is, by default, valid on all days.
If the startdate is equal to the enddate and the endtime is specified, the endtime value must be equal to or later than the starttime value.
For schedules with a repeats value of one day or more, the endtime value has no meaning and is ignored.
If endtime is not specified, the default is midnight.
If starttime is not specified, the default is 00:00am.
