question

oly-5173 avatar image
0 Votes"
oly-5173 asked TomPhillips-1744 answered

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-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

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

NaomiNNN avatar image
0 Votes"
NaomiNNN answered oly-5173 commented
· 1
5 |1600 characters needed characters left characters exceeded

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

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 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered TomPhillips-1744 converted comment to answer

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

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

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 Votes 0 ·

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 Votes 0 ·

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 Votes 0 ·
Show more comments
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

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

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

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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

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