October 23, 2013 at 5:34 pm
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
I would suggest doing a single job using an SSIS package with connections to both servers.
October 23, 2013 at 6:14 pm
Currently in our scenario this is nt possible.any other suggestions plz. Thanks fr the reply 🙂
October 23, 2013 at 6:29 pm
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
October 24, 2013 at 4:44 am
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
Thanks everyone for sharing the ideas. 🙂
October 24, 2013 at 12:15 pm
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
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