How to: Create a SQL Server Agent Master Job (Transact-SQL)
This topic describes how to use stored procedures to create a master Microsoft SQL Server Agent job.
For information about how the Windows account used for the SQL Server Agent service affects a multiserver environment, see Creating a Multiserver Environment.
To create a master SQL Server Agent job
Execute sp_add_job to create a job.
Execute sp_add_jobstep to create one or more job steps.
Execute sp_add_schedule to create a schedule.
Execute sp_attach_schedule to attach a schedule to the job.
Execute sp_add_jobserver to specify the target servers on which the job is to run.
Changes to master SQL Server Agent jobs must be propagated to all involved target servers. Because target servers do not initially download a job until sp_add_jobserver is called, Microsoft recommends that you complete all job steps and job schedules for a particular job before you execute sp_add_jobserver. Otherwise, sp_post_msx_operation must subsequently be called to request that the target servers download the modified job again.
Security
Distributed jobs that have steps which are associated with a proxy run under the context of the proxy account on the target server. Make sure that the following conditions are met or job steps that are associated with a proxy will not be downloaded from the master server to the target:
- The registry subkey \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance_name>\SQL Server Agent\AllowDownloadedJobsToMatchProxyName (REG_DWORD) is set to 1 (true). By default, this subkey is set to 0 (false).
- A proxy account exists on the target server that has the same name as the master server proxy account under which the job step runs.
If job steps that use proxy accounts fail when downloading them from the master server to the target server, you can check the error_message column in the sysdownloadlist table in the msdb database for the following error messages:
- "The job step requires a proxy account, however proxy matching is disabled on the target server."
To resolve this error, set the AllowDownloadedJobsToMatchProxyName registry subkey to 1. - "Proxy not found."
To resolve this error, make sure a proxy account exists on the target server that has the same name as the master server proxy account under which the job step runs.
See Also
Other Resources
System Stored Procedures (Transact-SQL)
sp_add_job (Transact-SQL)
sp_add_jobstep (Transact-SQL)
sp_add_schedule (Transact-SQL)
sp_attach_schedule (Transact-SQL)
sp_add_jobserver (Transact-SQL)
sp_post_msx_operation (Transact-SQL)
sp_msx_get_account (Transact-SQL)
sp_msx_set_account (Transact-SQL)