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