replacing time delay

oly 66

Hello Experts;

I have a stored procedure which is used to run a job. However, within my stored procedure I have an sql query which is used to get the status of the job, how can I make sure the job is completed first before the sql statement is ran. My code is structured as the following below

create procedure dbo.sprun_job(@Arjun singh _p varchar(45) out)
with execute as a caller
as execute as login = N'testuser'

exec msdb.dbo.msdbsprun_job
revert;

waitfor delay '00:00:30';

set @Arjun singh _p = (select run_status from msdb.dbo.sysjobhistory b)

if the waitfor delay is not there, it will say the job is still running, hence i want to make sure they job is done running before the run_status gets stored in info_p

How do I ensure that?

Thanks in advance

4 answers

Naomi 7,361 Reputation points

2022-04-29T18:13:25.273+00:00

Is it helpful?

https://www.sqlservercentral.com/blogs/how-to-start-sql-server-job-using-tsql-and-wait-for-it-to-complete
Please sign in to rate this answer.
oly 66 Reputation points

2022-04-29T20:51:26.41+00:00

this is similar to what i am doing expect it is using a table and waitfordelay. Hence is there another way to do things without using a waitfordelay.

THanks
Sign in to comment
Erland Sommarskog 101K Reputation points MVP

2022-04-29T21:18:06.373+00:00

Obviously, you will need to wait a while. And then you may have wait a while again. And again. A job could run for hours... So for a simple solution, you would need to poll it.

But that brings up the question: why do you want to this is the first place? If you want to wait for the result, why not run the action directly in your stored procedure? Why involve a job and make things complicated?
Please sign in to rate this answer.
oly 66 Reputation points

2022-04-29T23:16:06.567+00:00

A job is needed because it is an SSIS process that was deployed as a package. The SSIS process get activated through a front-end application by the push of a button.

Also I do not think i understand what you mean you have to wait for a while then wait a while again. The SSIS process is usually completed in 10 secs. it is very fast

Dan Guzman 9,206 Reputation points

2022-04-30T01:16:57.48+00:00

The SSIS process is usually completed in 10 secs. it is very fast

It might usually run in 10 seconds but there may be cases where it runs much longer (e.g. busy server). Conversely, It might run very fast and your users would be wait unnecessarily. You could poll for job completion in a loop using sp_help_job, checking for success or failure, with a shorter WAITFOR like 5 seconds.

oly 66 Reputation points

2022-04-30T02:01:17.17+00:00

so if I understand correctly do you mean I should write a while loop or a for loop checking the status continuously and only exit the loop when the status is completed..if you have a sample syntax that will greatly help as well

Erland Sommarskog 101K Reputation points MVP

2022-04-30T08:48:08.533+00:00

Note that rather looping in T-SQL, you could just as well have the loop in the application to call sp_help_job. Or have the SSIS package to write a completion row into some application table and poll that table.

oly 66 Reputation points

2022-04-30T15:11:53.06+00:00

So this is my first attempt writing the code below

create procedure dbo.sprun_job(@Arjun singh _p varchar(45) out)
with execute as a caller
as execute as login = N'testuser'

DECLARE @JobStatus INT

exec msdb.dbo.msdbsprun_job
revert;

SET @JobStatus = current_execution_status FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',
'EXEC MSDB.dbo.sp_help_job @Job _name = ''JobName'', @Job _aspect = ''JOB'' ')

WHILE @JobStatus = 4
BEGIN

WAITFOR DELAY '00:00:03'

SELECT @JobStatus = current_execution_status FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;', 'EXEC MSDB.dbo.sp_help_job @Job _name = ''JobName'', @Job _aspect = ''JOB'' ')

END

I got started on it reading a few articles online by Brian Bickell but haven't finalized anything yet
Sign in to comment
CathyJi-MSFT 21,091 Reputation points Microsoft Vendor

2022-05-02T10:06:53.657+00:00

Hi @oly ,

Please check if below thread could give some idea, hope this could help you.

sp_start_job wait for job to finish

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
Tom Phillips 17,716 Reputation points

2022-05-02T14:41:39.843+00:00

If all your Agent job is doing is running an SSIS package, you can do that directly from your proc and wait for it to finish.

See:
https://masudprogrammer.wordpress.com/2016/08/31/ssis-execute-a-pacakge-and-wait-until-package-execution-finished/
Please sign in to rate this answer.

0 comments No comments
Sign in to comment