Post reply

Run jobs in sequence in two different sql servers

  • October 23, 2013 at 5:34 pm

    #299500

    Hi All,

    I need some suggestions

    I got two servers

    Server A: SQL 2000

    Server B:SQL 2012

    The requirement is, ServerA has some job which is automated thru SQL Server agent, on Succes of this job on ServerA, I want to run another job in ServerB i.e SQL 2012. How to achieve this

    Any input is appreciated.

    Thanks

  • October 23, 2013 at 5:44 pm

    #1661123

    I would suggest doing a single job using an SSIS package with connections to both servers.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • October 23, 2013 at 6:14 pm

    #1661125

    Currently in our scenario this is nt possible.any other suggestions plz. Thanks fr the reply 🙂

  • October 23, 2013 at 6:29 pm

    #1661126

    I might be missing information to find the best option, but another idea that comes to me is to add a verifying step on second server at the beginning of the job that queries for information on msdb.dbo.sysjobhistory from first server.

    Someone more experienced on administration could give you better advices.:-D

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • October 24, 2013 at 4:44 am

    #1661211

    Add a powershell step to the job on server A that connects to server B and runs sp_start_job.

    The powershell script would look similar to this:

    Invoke-sqlcmd -ServerInstance "serverB" -Query "EXEC msdb.dbo.sp_start_job @name = 'myJobName'"

    Make sure you execute the step using a proxy account that has enough privileges on ServerB to start the job.

    Hope this helps

    Gianluca

    -- Gianluca Sartori

  • October 24, 2013 at 11:33 am

    #1661404

    Thanks everyone for sharing the ideas. 🙂

  • October 24, 2013 at 12:15 pm

    #1661418

    You cannot just execute sp_start_job - because all that does is starts the job and doesn't wait for the job to actually complete.

    I would create a job on the 2012 system as follows:

    Step 1: Powershell sub-system, use SQLCMD to call out to the 2000 system and execute the code you need run on that system. Do not start a job here - just execute the stored procedure or scripts...

    Step 2: Execute the procedure/code on the 2012 instance

    The only gotchas here is that you have to be sure you are returning valid error status from step 1 so that step 2 does not execute if step 1 fails.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • October 24, 2013 at 2:47 pm

    #1661470

    Jeffrey Williams 3188 (10/24/2013)

    You cannot just execute sp_start_job - because all that does is starts the job and doesn't wait for the job to actually complete.

    You're right. Peter Larsson (swePeso) wrote a stored procedure to wait for a job completion. You can find it here. There are many others around, just google "sql server wait job completion" and you will find plenty.

    Step 1: Powershell sub-system, use SQLCMD to call out to the 2000 system and execute the code you need run on that system. Do not start a job here - just execute the stored procedure or scripts...

    Step 2: Execute the procedure/code on the 2012 instance

    The only gotchas here is that you have to be sure you are returning valid error status from step 1 so that step 2 does not execute if step 1 fails.

    That's what I would do if I could choose.

    I think the op needed to keep both jobs, but I might be wrong.

    -- Gianluca Sartori

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply