I want the completion of a SQL Server agent job to trigger the start of a job on another server.
More specifically, I want my SSRS server to email a subscription report whenever an FTP/Import job completes on a different production server.
if I do:
EXEC msdb.dbo.sp_start_job @job_name = 'Remote job name', @server_name = 'Remote server'
The specified @job_name ('Remote Job Name') does not exist.
(maybe trying to run locally?)
if I do:
EXEC [Remote Server].msdb.dbo.sp_start_job @job_name = "Remote job name'
Could not connect to server 'RemoteServer' because 'RemoteUser' is not defined as a remote login at the server. Verify that you have specified the correct login name.
This user is one we use to query across linked servers all the time, can query across, and has db_owner (just to test) rights on msdb.