sp_add_job

Adds a job executed by the job scheduler.

Syntax

sp_add_job [ @job_name = ] 'job_name'  
     [ , [ @enabled = ] enabled ]   
     [ , [ @description = ] 'description' ]   
     [ , [ @start_step_id = ] step_id ]   
     [ , [ @category_name = ] 'category' ]   /* not supported */
     [ , [ @category_id = ] category_id ]    /* not supported */
     [ , [ @owner_login_name = ] 'login' ]   
     [ , [ @notify_level_eventlog = ] eventlog_level ] /* not supported */
     [ , [ @notify_level_email = ] email_level ]       /* not supported */
     [ , [ @notify_level_netsend = ] netsend_level ]   /* not supported */
     [ , [ @notify_level_page = ] page_level ]         /* not supported */
     [ , [ @notify_email_operator_name = ] 'email_name' ]     /* not supported */
     [ , [ @notify_netsend_operator_name = ] 'netsend_name' ] /* not supported */
     [ , [ @notify_page_operator_name = ] 'page_name' ]       /* not supported */
     [ , [ @delete_level = ] delete_level ]
     [ , [ @job_id = ] job_id OUTPUT ]

Arguments

[ @job_name = ] 'job_name'

The name of the job. The name must be unique. job_name is nvarchar(128), 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'

The description of the job. description is nvarchar(512), with a default of NULL.

[ @start_step_id = ] step_id

The identification number of the first step to execute for the job. step_id is int, with a default of 1.

[ @category_name = ] 'category' -- Not supported

Parameter is for SQL Server compatibility and its value is ignored.

[ @category_id = ] category_id -- Not supported

Parameter is for SQL Server compatibility and its value is ignored.

[ @owner_login_name = ] 'login'

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 -- Not supported

Parameter is for SQL Server compatibility and its value is ignored.

[ @notify_level_email = ] email_level -- Not supported

Parameter is for SQL Server compatibility and its value is ignored.

[ @notify_level_netsend = ] netsend_level -- Not supported

Parameter is for SQL Server compatibility and its value is ignored. 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 -- Not supported

Parameter is for SQL Server compatibility and its value is ignored.

[ @notify_email_operator_name = ] 'email_name' -- Not supported

Parameter is for SQL Server compatibility and its value is ignored.

[ @notify_netsend_operator_name = ] 'netsend_name' -- Not supported

Parameter is for SQL Server compatibility and its value is ignored.

[ @notify_page_operator_name = ] 'page_name' -- Not supported

Parameter is for SQL Server compatibility and its value is ignored.

[ @delete_level = ] delete_level

A value that indicates when to delete the job. delete_value is int, with a default of 0, which means never. delete_level can be one of the following values:

Value	Description
0	Never
1	On success
2 (default)	On failure
3	Always

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

The job identification number assigned to the job if created successfully. job_id is an output variable of type uniqueidentifier, with a default of NULL.

Result Sets

None

Remarks

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_schedule can be used to create the schedule for use to execute the job.

Lyftron Administrative Portal provides an easy, graphical way to manage jobs, and is the recommended way to create and manage jobs.

Permissions

To run this stored procedure, users must be a member of the sysadmin fixed server role.

Only members of the sysadmin fixed server role can set or change the value for @owner_login_name. If users who are not members of the sysadmin role set or change the value of @owner_login_name, execution of this stored procedure fails and an error is returned.

Examples

A. Add a job.

This example adds a new job named "RebuildCache".

EXEC dbo.sp_add_job 
    @job_name = N'RebuildCache';

B. Add a disabled job and get its identifier.

DECLARE @newJobId uniqueidentifier;
EXEC sp_add_job @job_name='example job', @enabled=0, @job_id = @newJobId output;

SELECT @newJobId;

See Also

« Previous Next »