Post reply

Job/JobStep Dependency

  • July 6, 2006 at 10:04 am

    #113531

    Hi,

    I'm sort of new to SQL Server Agent (and to this forum).

    Here's my problem.

    Server2 (Reporting/Test) has a multi-step job, restoring a database backup from Server1 (Production), extracting data (DTS) to various MS Access mdb's, and then initiating compact and repairs for these mdbs.

    Is there any way I can make this Server2 job dependent on the backup job on Server1, or even initiate the backup job on Server1 from the first jobstep in the job on Server2.

     

    I haved tried searching the forums for 'Job Dependency', but I have not found much.

     

    Any help would be appreciated,

    Wayne

     

  • July 7, 2006 at 9:14 am

    #648017

    Wayne

    (1) You could set up master and target servers so that all jobs are defined on one server.  Then you could combine the two jobs into one, with the execution of the second step depending on the success of the first.

    (2) Or you could look at creating a DTS package with individual steps that do what the individual jobs do now.  I haven't really thought that one through so it may not work.

    (3) Lastly, you could have the first job write to a text file or table, and have the second job check that before it runs.

    John

  • July 11, 2006 at 6:43 am

    #648434

    Thanks John,

     

    I've just implemented option 3 using a script I borrowed from sqlservercentral. It works fine as a temporary solution.

     

    When I find time I'll  look into the other options you mentioned.

     

     

     

    Wayne

  • June 8, 2007 at 1:53 pm

    #711794

    Wayne,

    I have the same requirement for not executing one job until another completes.

    One of my developers came up with the idea to use “Steps” within a “Job” to create a synchronous dependency chain. Each step by design executes one at a time within a job. So, with this you can move what you are executing in "job 2" to "Step 2" of "job 1" to force sequential versus parallel. I’ll send an update after we test this out. Should work pretty well.

    Peace out, hope this helps,

    Chris.

     

  • June 11, 2007 at 7:47 am

    #711995

    A method I've used in the past to do this is to have one of the jobs have no schedule and to have a step in the other job which uses OSQL or SQLCMD (invoked from xp_cmdshell) to run sp_start_job.

Viewing 5 posts - 1 through 4 (of 4 total)

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