Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    95

    Unanswered: How to run multiple dependent SQL Jobs with OSQL

    I want to execute multiple SQL Server scheduled jobs using OSQL, but I only want to execute a job if the previous job completed. Is this possible? So far I am unable to prevent all jobs from starting before any have completed.

    OSQL -Sservername -E -b -Q"usp_start_job 'JobNumber1'" -o usp_start_job.out
    IF ERRORLEVEL 1 GOTO ERROR

    OSQL -Sservername -E -b -Q"usp_start_job 'JobNumber2'" -o usp_start_job.out
    IF ERRORLEVEL 1 GOTO ERROR

    OSQL -Sservername -E -b -Q"usp_start_job 'JobNumber3'" -o usp_start_job.out
    IF ERRORLEVEL 1 GOTO ERROR
    GOTO EXIT

    :ERROR
    ECHO *** ERROR *** Check Log File

    :EXIT
    *** JOB COMPLETED ***

    Dave

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Create a new job that uses separate steps consisting of an sp_start_job to launch one of the other jobs. Each step will execute in turn, launching the appropriate job.

    -PatP

  3. #3
    Join Date
    Jan 2003
    Posts
    95
    I already tried that with no success. The logic looks as follows:

    sp_start_job 'DBA Test' -- Inserts one record into a table
    go
    sp_start_job 'DBA Test2' -- Waits 20 seconds and inserts a record
    go
    sp_start_job 'DBA Test3' -- Insert a record
    go

    All jobs execute immediately.

    Dave

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That looks suspiciously like a SQL script instead of a job with a set of job steps to me.

    -PatP

  5. #5
    Join Date
    Jan 2003
    Posts
    95
    Lightbulb.

    We had a DR test this week and apparently I'm still experiencing the affects. Thanks for the help. That should do the trick.

    Dave

  6. #6
    Join Date
    Jan 2003
    Posts
    95
    I spoke too soon. I added sp_start_job 'job name' to three different job steps and the same problem occurs. All three jobs are executed before any job completes. I believe the reason is due to sp_start_job. SQL Server successfully executes the command, which simply starts the job. It doesn't care whether or not the job it started has completed.

    Any suggestions.

    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •