Export (0) Print
Expand All
Expand Minimize

sp_apply_job_to_targets (Transact-SQL)

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

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

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

[ @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 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.

0 (success) or 1 (failure)

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.

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

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
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft