January 16, 2010 at 4:27 pm
:-PHello:hehe:
My post could be duplicate but somehow I couldn't find the answer I am looking for.
Here is my senecio.
I have my Server-A is Prod/ETL server where I have schedule nightly ETL process(SP's) that finishes 1AM Daily.
I also have my Production server-B where data get updated at 4AM Daily.
Now, What I would like to accomplish is to when my ETL Process finishes at 1AM on Server-A I would like to kick of data update process on Server-B. This both job has been schedule on "Job Activity Monitor" SQL 05.
So here is my time line:
1) Server-A ETL Process finish 1AM
2) Server-B Update Process start 4AM
Between this there is not validation that if ETL Process failed than update process on Server-B SHOULD NOT start.
So, How do I create a process that when Server-A ETL process finishes successfully THEN server-B update process kick off..other wise it should not run.
Please let me know if you can provide me some guidance.
January 16, 2010 at 7:51 pm
Server A could set a PASS/FAIL flag in a table on Server B.
Server B job checks the flag to see if it should run or not.
You might also want a date, to make sure the flag is from the same day, not accidentally left from the previous day.
I'm sure there are better ideas, but that popped into my head just now.
January 16, 2010 at 8:45 pm
Alright so let me see if I understood correctly,
When my schedule SP runs on Server A at the successful there should be a script that update a table. Lets call a LogTable where FLAG = Yer/No and based on Yes/No the Server B job will kick off.
Basically - what I am trying to accomplish is to create a process that will execute job on Server B when Server A job is successfully run otherwise it should not run on Server B if the failed on Server A.
I will try to work on logic from your above response but at meanwhile let me know if you have better idea.
Looking forwad somemore answerss...
January 16, 2010 at 9:09 pm
On server A on on success you can start other server job using
'sp_start_job'.
Create a linked server from Server A to Server B.
Map your agent accounts.
If ur linked server name is Serverb,then you can try using Serverb.msdb.dbo.sp_start_job
This will trigger the job on the Server B which is supposed to run at 4 AM.
I havent tried it personally and I am nt sitting in front of a SQL box. I cant vouch it. But I guess it shd help you.
Regards,
Raj
January 17, 2010 at 1:46 pm
You can also kick off job on server B using OSQL with Xp_cnmdshell by keeping this as second step of first job on server A
January 17, 2010 at 6:51 pm
OSQL is depreciated and will be removed in future versions.
XP_cmdshell is always a security concern.
Both of these shd be used as last options.
If you have a pressing need to use OSQL, use SQLCMD which is its replacement.
Regards,
Raj
January 17, 2010 at 7:11 pm
Guys this is great feed back.
Here is my action plan.
I will try to follow nagarajan instruction where I will use sp_start_job by using link server to kick off job on Server B. If that does not work than I will come back to option two whis is SQLCMD.
I should be try out this week and keep my post updated.
Thank you guys and I will put step instruction on which one is work for me.
Thank You,
Keyun
January 17, 2010 at 7:30 pm
I've been using sp_startjob for years and since sql 2000. Never had any issues after 3 versions installed on 5 client sites across at least 10 servers... you should be fine ;-).
January 17, 2010 at 8:03 pm
Ninja,
I have also used sp_start_job many times but not with linked servers.
So only didnt confirm it.;-)
Regards,
Raj
January 18, 2010 at 6:31 am
Guys..this is great.
First of all thank you very much to arr.nagaraj. I followed your instruction and SP_Start_Job did work with my two box.
Here is the step:
Created last step on ServerA with below command:
USE msdb
GO
EXEC serverA.msdb.dbo.sp_start_job 'MyStep'
GO
and thats it.. I watched on ServerB where job get executed without any error. This is great...
Thanks every one specially Nagaraj for helping me out.
Appreciated.
January 18, 2010 at 7:04 pm
Glad it worked..Welcome..our pleasure 🙂
Regards,
Raj
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply