sp_apply_job_to_targets (Transact-SQL)

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

Applies a job to one or more target servers or to the target servers belonging to one or more target server groups.

Topic link icon Transact-SQL Syntax Conventions

Syntax


sp_apply_job_to_targets { [ @job_id = ] job_id | [ @job_name = ] 'job_name' }  
     [ , [ @target_server_groups = ] 'target_server_groups' ]   
     [ , [ @target_servers = ] '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.

[ @job_name =] '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.

Note

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

[ @target_server_groups =] '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= ] 'target_servers'
A comma-separated list of target servers to which the specified job is to be applied. target_serversis nvarchar(2048), with a default of NULL.

[ @operation= ] 'operation'
Is whether the specified job should be applied to or removed from the specified target servers or target server groups. operationis 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

Only members of the sysadmin fixed server role can execute this procedure.

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  

See Also

sp_add_jobserver (Transact-SQL)
sp_delete_jobserver (Transact-SQL)
sp_remove_job_from_targets (Transact-SQL)
System Stored Procedures (Transact-SQL)