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.

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

Reference

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

Help and Information

Getting SQL Server 2005 Assistance