Post reply

How to execute job from Server A to Server B?

  • January 16, 2010 at 4:27 pm

    #214614

    :-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

    #1104655

    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

    #1104659

    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

    #1104661

    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

    http://Strictlysql.blogspot.com

  • January 17, 2010 at 1:46 pm

    #1104733

    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

    #1104752

    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

    http://Strictlysql.blogspot.com

  • January 17, 2010 at 7:11 pm

    #1104754

    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

    #1104759

    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

    #1104763

    Ninja,

    I have also used sp_start_job many times but not with linked servers.

    So only didnt confirm it.;-)

    Regards,
    Raj

    http://Strictlysql.blogspot.com

  • January 18, 2010 at 6:31 am

    #1104896

    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

    #1105200

    Glad it worked..Welcome..our pleasure 🙂

    Regards,
    Raj

    http://Strictlysql.blogspot.com

Viewing 11 posts - 1 through 10 (of 10 total)

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