replacing time delay

Question

question

oly-5173 asked Apr 29, '22 TomPhillips-1744 answered May 2, '22

replacing time delay

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(@info_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 @info_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

sql-server-general sql-server-transact-sql

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2022-04-29T18:13:25.273Z

NaomiNNN answered Apr 29, '22 oly-5173 commented Apr 29, '22

Is it helpful?

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

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

oly-5173 · Apr 29 at 08:51 PM

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

0 ·

Answer 2 · 2022-04-29T21:18:06.373Z

ErlandSommarskog answered Apr 29, '22 TomPhillips-1744 converted comment to answer May 2, '22

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?

· 5

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

oly-5173 · Apr 29 at 11:16 PM

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

0 ·

DanGuzman oly-5173 · Apr 30 at 01:16 AM

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.

0 ·

oly-5173 DanGuzman · Apr 30 at 02:01 AM

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

0 ·

Show more comments

Answer 3 · 2022-05-02T10:06:53.657Z

Cathyji-msft answered May 2, '22

Hi @oly-5173,

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

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 4 · 2022-05-02T14:41:39.843Z

TomPhillips-1744 answered May 2, '22

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/

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question