SQL Server Performance Forum – Threads Archive

How to execute multiple 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

I have run into this problem myself. The problem that you are running into is that the sp_start_job proc returns a return_status immediately following the execution of the proc. The return_status is only a status of whether the proc was able to start the job or not, not whether it completed successfully or not. You need to introduce into your logic error checking based on the sysjobhistory run_status column. 4 = In Progress, 0=failed, 1=success, 2=retry, 3=cancelled. Once the status <> 4 and = 1 then exec the next job. I would set it up in a single proc with input parameters for the jobs in the order you want to run them. (EX: create proc usp_start_jobs @job1 varchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />=NULL, @job2 varchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />=NULL,…) Need more direction? Post the question. Zach http://www.sqljunkies.com/WebLog/odds_and_ends/</a>

That’s what I was afraid of. One problem is a job could take 45 minutes to 1 hour to complete. Should I take the approach below or do you have another suggestion? Set @Job_Name = First job to run
Select @Job_Id=job_id
From msdb..sysjobs
Where name = @Job_Name EXEC sp_start_job @Job_Name Select @Run_Status = run_status
from msdb..sysjobhistory
where Job_Id = @Job_Id While @Run_Status <> 1 AND @Run_Status <> 2 — Check success or failure
Begin WAITFOR DELAY ’00:05:00′ — Wait 5 minutes and check again Select @Run_Status = run_status
From msdb..sysjobhistory
Where Job_Id = @Job_Id End If @Run_Status = 1 — Success
EXEC sp_start_job @Job_Name
Else
RAISERROR ‘*** ERROR *** Job Failed’,16,1 Set @Job_Name = Next job to run
Select @Job_Id=job_id
from msdb..sysjobs
where name = @Job_Name Repeat the Loop

It seems to be reasonable to wait until the job finishes, try it and let us know. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>