February 18, 2010 at 9:44 am
Hi,
I want to run a job on another server B, after the job on the server A finishes. I kind of started with SSIS, checking run_status value from job history and if it is 1 in execute sql task and if yes, then run the remaining tasks in SSIS package. Sometime the job on server B may go longer and I have to keep checking for the status. What is best solution for this complete scenario?
Thank you,
VG
February 18, 2010 at 11:56 am
Combine all the steps into one job. If the steps that run stuff on the other server don't always run those steps then have a step in the middle that does a condition check and fail the step on the condition. Make the job succeed on failure of that particular step.
For the steps that run stuff on the other server either call SSIS packages or run SQLCMD. You will of course have to grant permsisions for the host to run stuff on the remote server.
Regards,
Toby
February 18, 2010 at 12:05 pm
For the steps to run on the other server - can not be handled by SSIS, since it a executable and it should run on the other server, after this exe completes we have to run remaining steps on this server by connecting to other server, which can be hanled by SSIS.
-VG
February 18, 2010 at 12:06 pm
I am sure I have not understand it completely.
If you have to start a different job upon completion of the current job, I would rather add a final step in the job1 on server1 which kick of the job2 on server2.
Using.
EXEC LinkedServer.msdb.dbo.sp_start_job N'Job2' ;
GO
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 18, 2010 at 12:07 pm
I've used this method:
Have the Server A job update a table on Server B when it completes, then have the Server B job query the table in it's first step and continue or fail based on what the query returns.
You might also check out Multi-server administration in BOL. I've never used it so I don't know the pros and cons, but this is the kind of thing it's made for.
Greg
February 18, 2010 at 12:13 pm
If the commands in the exe that must run in the other server can be broken out into T-SQL then you will have a solution. If they cannot then you are stuck with having to check status, because as you said, you must run that exe on the other server. A job on the other server must be used except for one other possible solution that would require xp_cmdshell.
If a procedure calls the exe from xp_cmdshell on the other server, and this procedure is called from a remote job you may be able to get it that way if the procedure waits for the exe to complete instead of executin asynchroniously.
I see your predicament though, and maybe the SSIS check status method is good enough.
Regards,
Toby
February 18, 2010 at 4:30 pm
I am suggested to use Remote Procedure Call for this...Not sure how?
I know, this is possible by Linked server, which is my last option...
-VG
February 18, 2010 at 6:11 pm
Yes you need to create a linked server with RPC settings enabled.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 19, 2010 at 2:27 pm
Is it possible to use OPENROWSET to call an exe on the other server?
-VG
February 23, 2010 at 2:11 am
hi Bru
is there any way that rather than adding last sep in job1
i want to add step 1 in job 2 on server 2 to check job 1 on server 1 is ruuning or not once job1 finishes job to should execute
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply