Job/JobStep Dependency

Question

Job/JobStep Dependency

Wayne Barr-341264

Newbie

Points: 2
More actions
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

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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

Wayne Barr-341264 Newbie Points: 2 More actions · Answer 2

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

Christopher Cuadras Newbie Points: 1 More actions · Answer 3

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.

Phil C-257913 SSC-Addicted Points: 453 More actions · Answer 4

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.