Bagikan melalui

  • Artikel

sp_add_job (Transact-SQL)

Adds a new job executed by the SQLServerAgent service.

Topic link iconTransact-SQL Syntax Conventions

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_idOUTPUT ]

Arguments

  • [ @job_name = ] 'job_name'
    The name of the job. The name must be unique and cannot contain the percent (%) character. job_nameis nvarchar(128), with no default.

  • [ @enabled = ] enabled
    Indicates the status of the added job. enabledis 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. If description is omitted, "No description available" is used.

  • [ @start_step_id = ] step_id
    The identification number of the first step to execute for the job. step_idis int, with a default of 1.

  • [ @category_name = ] 'category'
    The category for the job. categoryis sysname, with a default of NULL.

  • [ @category_id = ] category_id
    A language-independent mechanism for specifying a job category. category_idis int, with a default of NULL.

  • [ @owner_login_name = ] 'login'
    The name of the login that owns the job. loginis sysname, with a default of NULL, which is interpreted as the current login name. 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.

  • [ @notify_level_eventlog = ] eventlog_level
    A value indicating when to place an entry in the Microsoft Windows application log for this job. eventlog_levelis 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
    A value that indicates when to send an e-mail upon the completion of this job. email_levelis int, with a default of 0, which indicates never. email_leveluses the same values as eventlog_level.

  • [ @notify_level_netsend = ] netsend_level
    A value that indicates when to send a network message upon the completion of this job. netsend_levelis int, with a default of 0, which indicates never. netsend_level uses the same values as eventlog_level.

  • [ @notify_level_page = ] page_level
    A value that indicates when to send a page upon the completion of this job. page_levelis int, with a default of 0, which indicates never. page_leveluses the same values as eventlog_level.

  • [ @notify_email_operator_name = ] 'email_name'
    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'
    The name of the operator to whom the network message is sent upon completion of this job. netsend_nameis sysname, with a default of NULL.

  • [ @notify_page_operator_name = ] 'page_name'
    The name of the person to page upon completion of this job. page_nameis sysname, with a default of NULL.

  • [ @delete_level = ] delete_level
    A value that indicates when to delete the job. delete_valueis int, with a default of 0, which means never. delete_leveluses 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_idOUTPUT
    The job identification number assigned to the job if created successfully. job_idis an output variable of type uniqueidentifier, 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 the SQL Server Agent service uses to execute the job. Use sp_add_jobserver to set the SQL Server instance where the job executes, and sp_delete_jobserver to remove the job from the SQL Server instance.

If the job will execute on one or more target servers in a multiserver environment, use sp_apply_job_to_targets to set the target servers or target server groups for the job. To remove jobs from target servers or target server groups, use sp_remove_job_from_targets.

SQL Server Management Studio provides an easy, graphical way to manage jobs, and is the recommended way to create and manage the job infrastructure.

Permissions

To run this stored procedure, users must be a member of the sysadmin fixed server role, or be granted one of the following SQL Server Agent fixed database roles, which reside in the msdb database:

  • SQLAgentUserRole

  • SQLAgentReaderRole

  • SQLAgentOperatorRole

For information about the specific permissions that are associated with each of these fixed database roles, see SQL Server Agent Fixed Database Roles.

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. Adding a job

This example adds a new job named NightlyBackups.

USE msdb ;
GO

EXEC dbo.sp_add_job
    @job_name = N'NightlyBackups' ;
GO

B. Adding a job with pager, e-mail, and net send information

This example creates a job named Ad hoc Sales Data Backup that notifies François Ajenstat (by pager, e-mail, or network pop-up message) if the job fails, and deletes the job upon successful completion.

Note

This example assumes that an operator named François Ajenstat and a login named françoisa already exist.

USE msdb ;
GO

EXEC dbo.sp_add_job
    @job_name = N'Ad hoc Sales Data Backup', 
    @enabled = 1,
    @description = N'Ad hoc backup of sales data',
    @owner_login_name = N'françoisa',
    @notify_level_eventlog = 2,
    @notify_level_email = 2,
    @notify_level_netsend = 2,
    @notify_level_page = 2,
    @notify_email_operator_name = N'François Ajenstat',
    @notify_netsend_operator_name = N'François Ajenstat', 
    @notify_page_operator_name = N'François Ajenstat',
    @delete_level = 1 ;
GO