MyDoggieJessie (12/21/2012)
John Mitchell-245523 (12/21/2012)
I would guess that you can't start a job remotely via a linked server. You could try using Powershell or SSIS instead. Or you could set up some triggers on the remote server so that they fire jobs when you update a table.John
Incorrect. You can very easily start a job remotely on a linked server. I would however, recommend you that you use the job uniqueidentifier rather than passing in the job name itself (although it should work either way) - what options did you configure for your linked server?
Example:
DECLARE @job_id uniqueidentifier
SET @job_id = '80209C18-A8AD-48BA-8D6B-10D785FE4383'
EXECUTE [LinkedServer].msdb.dbo.sp_start_job @job_id
I am not sure how that would work well in a multi-DBA environment. If someone drops and recreates the job it should not break other processes, provided the same name is retained. How would you always know the unique id?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato