Is it helpful?
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(@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
Sort by: Most helpful
-
-
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?
-
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".
-
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.