Tuesday, November 22, 2011

How to start SQL job of another server

I got a task to trigger a job of Server B when job of Server A completes. One way of doing is we can monitor how much time job is taking in Server A and accordingly we can schedule job of Server B. But here there might situations where the jobs take longer than expected time, if Server A job is taking only 5mins to complete and it is scheduled to start at 9AM and we have scheduled Server B job to run at 9:30AM and due to some reason Server A job takes more than 30-45mins and still didn’t completed even then the job of Server B starts by 9:30AM which was not supposed to happen.

So I thought to trigger the job of ServerB remotely. So plan is to trigger the Server B job once Server A job completes. To accomplish this we can use several ways and I have used command ‘sp_start_job’.

Sp_start_job serverB.msdb.dbo.sp_start_job @job_name = ‘Remotejob’

The above command when I ran on ServerA it has thrown below error:

Msg 7202, Level 11, State 2, Line 1
Could not find server 'ServerB' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Which clearly shows I have missed a basic thing of linking servers. We can link servers using sp_addlinkedserver. I have linked servers using below command and after I was able to see both servers names in sys.servers  table.

sp_addlinkedserver 'ServerB'

Now when I ran the above sp_start_job command again I received below error

Msg 7411, Level 16, State 1, Line 1
Server 'ServerB' is not configured for RPC.

What is this RPC? Its Remote Procedure Calls. When I checked the properties of Linked Servers which is available under Server Objects -> Linked Servers -> Providers -> ServerB (Right Click properties) -> Server Options

Here there are 2 options RPC and RPC Out, I have changed the ‘RPC Out’ option to TRUE which was FALSE. After setting the option to TRUE on both servers I ran the query again and this time it went fine.

As per main task to trigger server B job automatically I have added the command  Sp_start_job serverB.msdb.dbo.sp_start_job @job_name = ‘Remotejob’
as final step of the job in Server A, so automatically the last step of Server A job triggering the job of Server B.

No comments:

Post a Comment