To start a SQL Job after one other SQL job is completed

Question

Post reply

To start a SQL Job after one other SQL job is completed

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
September 29, 2014 at 5:53 am

#293721

Hi,
I'm looking for a T SQL query to start a SQL Job once another job is completed. Kindly help if you have any handy code.
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

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

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

exec msdb.dbo.sp_start_job @job_name = 'enter you jobname here'

You can enter this code as the last step of the first job. Change the text string to the name of the second job.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 2

SSCrazy,

I dont think what yopu'd suggested would work...as unless the first job is completed, I do not want to start the second job.

Thanks.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

SQL-DBA-01 (9/29/2014)
SSCrazy,
I dont think what yopu'd suggested would work...as unless the first job is completed, I do not want to start the second job.

If you put that TSQL statement as the last step of the first job, it will run only when all previous steps of the job have run successfully.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

HanShi SSC-Dedicated Points: 33507 More actions · Answer 4

Koen Verbeeck (9/29/2014)
SQL-DBA-01 (9/29/2014)
SSCrazy,
I dont think what yopu'd suggested would work...as unless the first job is completed, I do not want to start the second job.
If you put that TSQL statement as the last step of the first job, it will run only when all previous steps of the job have run successfully.

As Koen mentioned, all steps in a single job are executed in serial. In the options of each step you can define how it must proceed (on success or on failure: proceed to another step or quit the job).

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

SQL-DBA-01 (9/29/2014)
SSCrazy,
I dont think what yopu'd suggested would work...as unless the first job is completed, I do not want to start the second job.

Yup this will work. I use it often enough in many cases for clients.

An example of when I have used it is to kick off a restore job on a different server (via linked server) as soon as the backup is done.:cool:

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

djj SSCoach Points: 18831 More actions · Answer 6

If you want a T-SQL way to tell if a job is done look at sysjobhistory and sysjobs. The suggestions above however, would be a better way to do it.

maddy192 SSC-Addicted Points: 484 More actions · Answer 7

Sequential job steps would not work if you use sp_start _ob since sp_start_job succeeds once the job is started. SQL Agent will not wait for the job to finish before moving on to next step. You will have to use a loop to keep checking sp_help_job or MSDB tables to determine if the job is still running then wait for delay and eventually start second job or run another TSQL block after the job is finished.

Regarding the backup - restore someone mentioned, I do it too but I do a backup using TSQL and then call restore job. Remember to make it simple if you run a Query in Query Editor (QE) window and it keeps running ( like a backup would) next step will wait. In case of sp_start_job you'll get a result immediately when you run in QE window saying 'command(s) completed successfully'.

Hope that make sense !

Chinnappan Balan SSC-Addicted Points: 461 More actions · Answer 8

Below Script work well for this cause, It will wait for the first job to complete and then star the next job.

EXEC dbo.sp_start_job N'JobName';
GO

-- Script to wait for this job to complete before starting the next job.
Declare @Loop int
set @loop = 1

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
DROP TABLE #Results

CREATE TABLE #results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)

While @Loop = 1
BEGIN

WAITFOR DELAY '00:01:00'

DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @job_owner sysname

SELECT @job_id = job_id FROM msdb.dbo.sysjobs
WHERE name = 'JobName'

SELECT @job_owner = SUSER_SNAME()

INSERT INTO #results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id

SET @Loop = (SELECT TOP 1 running FROM #results)

TRUNCATE TABLE #results

END