sp_apply_job_to_targets (Transact-SQL)
Applies to:
SQL Server
Applies a job to one or more target servers or to the target servers belonging to one or more target server groups.
Transact-SQL syntax conventions
Syntax
sp_apply_job_to_targets
[ [ @job_id = ] 'job_id' ]
[ , [ @job_name = ] N'job_name' ]
[ , [ @target_server_groups = ] N'target_server_groups' ]
[ , [ @target_servers = ] N'target_servers' ]
[ , [ @operation = ] 'operation' ]
[ ; ]
Arguments
[ @job_id = ] 'job_id'
The job identification number of the job to apply to the specified target servers or target server groups. @job_id is uniqueidentifier, with a default of NULL
.
Either @job_id or @job_name must be specified, but both can't be specified.
[ @job_name = ] N'job_name'
The name of the job to apply to the specified the associated target servers or target server groups. @job_name is sysname, with a default of NULL
.
Either @job_id or @job_name must be specified, but both can't be specified.
[ @target_server_groups = ] N'target_server_groups'
A comma-separated list of target server groups to which the specified job is to be applied. @target_server_groups is nvarchar(2048), with a default of NULL
.
[ @target_servers = ] N'target_servers'
A comma-separated list of target servers to which the specified job is to be applied. @target_servers is nvarchar(2048), with a default of NULL
.
[ @operation = ] 'operation'
Specifies whether the specified job should be applied to or removed from the specified target servers or target server groups. @operation is varchar(7), with a default of APPLY
. Valid operations are APPLY
and REMOVE
.
Return code values
0
(success) or 1
(failure).
Remarks
sp_apply_job_to_targets
provides an easy way to apply (or remove) a job from multiple target servers, and is an alternative to calling sp_add_jobserver
(or sp_delete_jobserver
) once for each target server required.
Permissions
This stored procedure is owned by the db_owner role. You can grant EXECUTE
permissions for any user, but these permissions may be overridden during a SQL Server upgrade.
Examples
The following example applies the previously created Backup Customer Information
job to all the target servers in the Servers Maintaining Customer Information
group.
USE msdb;
GO
EXEC dbo.sp_apply_job_to_targets
@job_name = N'Backup Customer Information',
@target_server_groups = N'Servers Maintaining Customer Information',
@operation = N'APPLY' ;
GO