Aniket Narvankar

Aniket Narvankar

  • 533
  • 2.1k
  • 602.6k

SQL Server Job

Aug 21 2024 6:02 AM

Hi I am creating a sql server job

in job step i am calling a store procedure.

This step should retry 50 times at interval of 3 minutes if status is R. after 50 retries if status is not Y it will failed
If status is N it should fail immediately. now this status N can become at any time. it can become at first retry or 50th retry.
If Status is Y it should be success.

Following is procedure for reference

Create procedure sp_test

as

begin

decalre @flag char(1)

set @flag = (Select status from tbl1)

if(@flag = 'N')

begin

    @RaiseError("Job Failed",11,1)

end

else if(@flag='R')

begin

  @RaiseError("Running",11,1)

end

else if (@flag='Y')
begin

    print 'Success'

end
end

this sp_Test procedure i am calling from job step

now as we can see it is reading status from tbl
what is occuring right now
first try flag is R it will raise error , start second try. if it till 50 retries flag is R it will fail. this is correct
now if at any retry till 50 flag becomes Y it will succeed
the scenario which is failing is for N
for N also it will retry till all 50 tries and then it will fail
i want to stop the execution of job and give fail message as soon as status become N

how to do this. please let me know

i have tried using sp_Stop job which is in msdb db,also increase severity of raiseerror,also tried writing select 1/0 for status N. it is not working.
the ask is retries should stop immediately when status is N and report failure


Answers (2)