2

When you script a SQL agent job, there will be a sp_add_jobserver invocation in the script. I checked the documentation about this stored procedure but still can't understand what is a job server. As I understand, when you create a job, you are always create it on a specific server. Later you can run the job on that server. So why we need to use this stored procedure to combine a job to a server?

Just a learner
  • 26,690
  • 50
  • 155
  • 234
  • 1
    “when you create a job, you are always create it on a specific server. Later you can run the job on that server”,actually,it's not.When you create a job without sp_add_jobserver,the job will be in the job list of the target server,but it could not be executed successly.An error will ocurred: "Cannot start the job "XXX" (ID xxxx-xxx-xxxx-xxxx-xxxxx) because it does not have any job server or servers defined. Associate the job with a job server by calling sp_add_jobserver." – wangguanguo Jan 27 '18 at 05:49
  • @wangguanguo so if I deploy a job on server a and use sp_add_jobserver to combine the job with server a, I can successfully run the job on server a. But what if I combine the job with another server b? In that case the job is on a (there is no job definition on b) and I combined it with b on server a. If I start the job in this case, what will happen? – Just a learner Jan 27 '18 at 05:57
  • a job cannot both on a and b.If you sp_add_jobserver a job to b you will get an error: This job is currently targeted at a remote server so cannot also be targeted at the local server.You must delete the job in "a" first. After doing this ,the job is in the list of [SQL Server Agnet]-[Jobs]-[Multi-Server Jobs] in SSMS.You can execute it,and it will affect the server b. – wangguanguo Jan 27 '18 at 07:07

1 Answers1

3

To explan more details in here: To add a job to a different server,you must make a server the master server,and add target server(s) to it.After doing this,the SQL Server Agent in SSMS will become this:

SQL Server Agent MSX

The DelPDFHis job is target on another server: Job Properties of targets page

And the target server will download it to local. Target server Status

enter image description here

When executing the job in server a,it will execute in server b too.

wangguanguo
  • 181
  • 4