2

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' I receive: 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' I receive: 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.

0
2

If you're on SQL Server Enterprise Edition:

You could set up a Data-driven subscription in SSRS. On your database with the FTP/Import Job, the system database MSDB contains a table called sysjobhistory. On your SSRS server, set up a Data-driven subscription to run when the step in your FTP/Import job reports success to this table.

Instructions for setting up a Data-driven subscription in SSRS: Books Online

If you are on Standard Edition:

Please use a linked server as outlined by Martin Smith here:

Data Driven subscription is an Enterprise Edition feature. Just creating a regular subscription with a schedule in the past and calling EXEC LinkedServer.ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='whatever' would do what they need.

To implement Martin's suggestion, use the SQL Server agent job that is created automatically by SSRS when a subscription is created. The job's name will be capital letters and numbers, separated by hypens.

You should create this subscription, but then alter the job itself to have a schedule in the past, where it will not run. When you call this dbo.AddEvent over your linked server, you can add a new run time to this job

0
2

You can use sp_start_job stored procedure to start a job from any server using the server_name parameter. Passing nothing to this parameter means the job is on the local server but you should be able to pass your remote instance name and have it call the job on that server.

The only thing I think is you might have to use a run as condition on the job step, under the advanced properties. Otherwise the service account on your local instance would need the appropriate access on that remote instance.

Your command would look something like: sp_start_job @job_name= remote job name', @server_name='remote server instance'

Additional:

If you add this as a step to your job just make sure the previous step is set to only "execute next step" on successful action. On failure for your previous step should be set to stop job with as failure, based on your requirements.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.