sp_add_job
Adds a new job executed by the SQLServerAgent service.
Syntax
sp_add_job [ @job_name = ] 'job_name'
[ , [ @enabled = ] enabled ]
[ , [ @description = ] 'description' ]
[ , [ @start_step_id = ] step_id ]
[ , [ @category_name = ] 'category' ]
[ , [ @category_id = ] category_id ]
[ , [ @owner_login_name = ] 'login' ]
[ , [ @notify_level_eventlog = ] eventlog_level ]
[ , [ @notify_level_email = ] email_level ]
[ , [ @notify_level_netsend = ] netsend_level ]
[ , [ @notify_level_page = ] page_level ]
[ , [ @notify_email_operator_name = ] 'email_name' ]
[ , [ @notify_netsend_operator_name = ] 'netsend_name' ]
[ , [ @notify_page_operator_name = ] 'page_name' ]
[ , [ @delete_level = ] delete_level ]
[ , [ @job_id = ] job_id OUTPUT ]
Arguments
[ @job_name = ] 'job_name'
Is the name of the job. The name must be unique and cannot contain the percent (%) character. job_name is sysname, with no default.
[ @enabled = ] enabled
Indicates the status of the added job. enabled is tinyint, with a default of 1 (enabled). If 0, the job is not enabled and does not run according to its schedule; however, it can be run manually.
[ @description = ] 'description'
Is the description of the job. description is nvarchar(512), with a default of NULL. If description is omitted, "No description available" is used.
[ @start_step_id = ] step_id
Is the identification number of the first step to execute for the job. step_id is int, with a default of 1.
[ @category_name = ] 'category'
Is the category for the job. category is sysname, with a default of NULL.
[ @category_id = ] category_id
Is a language-independent mechanism for specifying a job category. category_id is int, with a default of NULL.
[ @owner_login_name = ] 'login'
Is the name of the login that owns the job. login is sysname, with a default of NULL, which is interpreted as the current login name.
[ @notify_level_eventlog = ] eventlog_level
Is a value indicating when to place an entry in the Microsoft® Windows NT® application log for this job. eventlog_level is int, and can be one of these values.
| Value | Description |
|---|---|
| 0 | Never |
| 1 | On success |
| 2 (default) | On failure |
| 3 | Always |
[ @notify_level_email = ] email_level
Is a value that indicates when to send an e-mail upon the completion of this job. email_level is int, with a default of 0, which indicates success. email_level uses the same values as eventlog_level.
[ @notify_level_netsend = ] netsend_level
Is a value that indicates when to send a network message upon the completion of this job. netsend_level is int, with a default of 0, which indicates never. netsend_level uses the same values as eventlog_level.
[ @notify_level_page = ] page_level
Is a value that indicates when to send a page upon the completion of this job. page_level is int, with a default of 0, which indicates never. page_level uses the same values as eventlog_level.
[ @notify_email_operator_name = ] 'email_name'
Is the e-mail name of the person to send e-mail to when email_level is reached. email_name is sysname, with a default of NULL.
[ @notify_netsend_operator_name = ] 'netsend_name'
Is the name of the operator to whom the network message is sent upon completion of this job. netsend_name is sysname, with a default of NULL.
[ @notify_page_operator_name = ] 'page_name'
Is the name of the person to page upon completion of this job. page_name is sysname, with a default of NULL.
[ @delete_level = ] delete_level
Is a value that indicates when to delete the job. delete_value is int, with a default of 0, which means never. delete_level uses the same values as eventlog_level.
Note When delete_level is 3, the job is executed only once, regardless of any schedules defined for the job. Furthermore, if a job deletes itself, all history for the job is also deleted.
[ @job_id = ] job_id OUTPUT
Is the job identification number assigned to the job if created successfully. job_id is an output variable of type uniqueidentifer, with a default of NULL.
Return Code Values
0 (success) or 1 (failure)
Result Sets
None
Remarks
@originating_server exists in sp_add_job, but is not listed under Arguments. @originating_server is reserved for internal use.
After sp_add_job has been executed to add a job, sp_add_jobstep can be used to add steps that perform the activities for the job. sp_add_jobschedule can be used to create the schedule that SQLServerAgent service uses to execute the job.
SQL Server Enterprise Manager provides an easy, graphical way to manage jobs, and is the recommended way to create and manage the job infrastructure.
Permissions
Execute permissions default to the public role.
Examples
A. Add a job
This example adds a new job named NightlyBackups.
USE msdb
EXEC sp_add_job @job_name = 'NightlyBackups'
B. Add a job with pager, e-mail, and net send information
This example creates a job named Ad hoc Sales Data Backup that notifies janetl (by pager, e-mail, or network pop-up message) if the job fails, and deletes the job upon successful completion.
USE msdb
EXEC sp_add_job @job_name = 'Ad hoc Sales Data Backup',
@enabled = 1,
@description = 'Ad hoc backup of sales data',
@owner_login_name = 'janet1',
@notify_level_eventlog = 2,
@notify_level_email = 2,
@notify_level_netsend = 2,
@notify_level_page = 2,
@notify_email_operator_name = 'janet1',
@notify_netsend_operator_name = 'janet1',
@notify_page_operator_name = 'janet1',
@delete_level = 1