Targets the specified job at the specified server.
Applies to: SQL Server (SQL Server 2008 through current version).
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:
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.
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.
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