sp_add_jobserver (Transact-SQL)

 

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Targets the specified job at the specified server.

Topic link icon Transact-SQL Syntax Conventions

  
sp_add_jobserver [ @job_id = ] job_id | [ @job_name = ] 'job_name'  
     [ , [ @server_name = ] 'server' ]   

[ @job_id = ] job_id
The identification number of the job. job_id is uniqueidentifier, with a default of NULL.

[ @job_name = ] 'job_name'
The name of the job. job_name is sysname, with a default of NULL.

System_CAPS_ICON_note.jpg Note


Either job_id or job_name must be specified, but both cannot be specified.

[ @server_name = ] 'server'
The name of the server at which to target the job. server is nvarchar(30), with a default of N'(LOCAL)'. servercan be either (LOCAL) for a local server, or the name of an existing target server.

0 (success) or 1 (failure)

None

@automatic_post exists in sp_add_jobserver, but is not listed under Arguments. @automatic_post is reserved for internal use.

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

By default, members of the sysadmin fixed server role can execute this stored procedure. Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb database:

  • SQLAgentUserRole

  • SQLAgentReaderRole

  • SQLAgentOperatorRole

For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles.

Only members of the sysadmin fixed server role can execute sp_add_jobserver for jobs that involve multiple servers.

A. Assigning a job to the local server

The following example assigns the job NightlyBackups to run on the local server.

System_CAPS_ICON_note.jpg Note


This example assumes that the NightlyBackups job already exists.

USE msdb ;  
GO  
  
EXEC dbo.sp_add_jobserver  
    @job_name = N'NightlyBackups' ;  
GO  

B. Assigning a job to run on a different server

The following example assigns the multiserver job Weekly Sales Backups to the server SEATTLE2.

System_CAPS_ICON_note.jpg Note


This example assumes that the Weekly Sales Backups job already exists and that SEATTLE2 is registered as a target server for the current instance.

USE msdb ;  
GO  
  
EXEC dbo.sp_add_jobserver  
    @job_name = N'Weekly Sales Backups',  
    @server_name = N'SEATTLE2' ;  
GO  

sp_apply_job_to_targets (Transact-SQL)
sp_delete_jobserver (Transact-SQL)
System Stored Procedures (Transact-SQL)

Community Additions

ADD
Show: