sp_add_jobserver (T-SQL)

Targets the specified job at the specified server.

Syntax

sp_add_jobserver [@job_id =] job_id | [@job_name =] 'job_name'
    [, [@server_name =] 'server' ]

Arguments

[@job_id =] job_id

Is the identification number of the job. job_id is uniqueidentifer, with a default of NULL.

[@job_name =] 'job_name'

Is the name of the job. job_name is sysname, with a default of NULL.

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

[@server_name =] 'server'

Is the name of the server at which to target the job. server is nvarchar(30), with a default of N’(LOCAL)’. server can be either (LOCAL) for a local server, or the name of an existing target server.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Remarks

@automatic_post exists in sp_add_jobserver but is not listed under Arguments. @automatic_post is reserved for internal use.

SQL Server Enterprise Manager provides an easy, graphical way to manage jobs, and is the recommended way to create and manage the job infrastructure.

Permissions

Execute permissions default to the public role for local jobs. Only members of the sysadmin fixed server role can execute sp_add_jobserver for multiserver jobs.

Examples

This example assigns the SEATTLE2 server to the multiserver job, Weekly Sales Data Backup job.

Note This example assumes that the Weekly Sales Data Backup job already exists.

USE msdb

EXEC sp_add_jobserver @job_name = 'Weekly Sales Data Backup',
    @server_name = 'SEATTLE2'

See Also