replacing time delay

Question

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

0 comments

4 answers

Your answer

Answer 1

Naomi Nosonovsky 8,906

Is it helpful?

https://www.sqlservercentral.com/blogs/how-to-start-sql-server-job-using-tsql-and-wait-for-it-to-complete

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

Answer 2

Tom Phillips 17,786

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/

0 comments

Answer 3

CathyJi-MSFT 22,426 Microsoft External Staff

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".

0 comments

Answer 4

Erland Sommarskog 134.3K MVP Volunteer Moderator

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?

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,516 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 134.3K Reputation points MVP Volunteer Moderator

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

Share via

replacing time delay

4 answers

Your answer