July 6, 2020 at 10:55 am
I need to write a query which will first update the table, then execute the job and if the job is successful, again update statement.
This would be a loop.
this is how it looks
DECLARE @dttm datetime
DEClare @datetable table (dttm datetime)
insert into @datetable values
('2017-01-01 00:00:00.000'),
('2018-01-01 00:00:00.000'),
('2019-01-01 00:00:00.000')
DECLAre Upddate cursor for
select dttm from @datetable
OPEN Upddate
FETCH NEXT from Upddate into @dttm
WHILE @@FETCH_STATUS = 0
BEGIN
--
print @dttm
Update Doc.conf
set setupdate = @dttm
SELECT @dttm = @dttm + 1;
EXEC msdb.dbo.sp_start_job @job_name= "myjob1"
print 'job sucecssful' +@dttm
FETCH NEXT FROM Upddate INTO @dttm
END;
CLOSE Upddate;
DEALLOCATE Upddate;
The loop works fine, but how do I handle if the job fails then dont execute another update and execute next update only when first job successfully completes
July 6, 2020 at 11:54 am
You would need to add a check to the sysjobhistory table in the MSDB database for the job you kicked off and check the status on it. If its a 0 then its failed and you would RAISERROR out and keep checking until the value is 1 for success then fetch next from the cursor to iterate into the next date.
July 6, 2020 at 12:08 pm
yea added that step:
select run_status = @runstatus from msdb.dbo.sysjobhistory jh
inner join msdb.dbo.sysjobs jb on
jb.job_id = jh.job_id
where jb.name = 'myjob1'
and run_status = 1
if @runstatus = 1
update ....
But still it fails.
SQLServerAgent Error: Request to run job xxx (from User yyyy) refused because the job already has a pending request from User yyyy
The job usually take 3 hrs to run but still it is returning the status as 1
what can be checked here
July 6, 2020 at 12:16 pm
In addition to anthony.green, you'll probably need a while loop where you'll have WAITFOR that would make delays between job status checks. Don't forget to add some logic to exit the loop after some time if that check would take too long. So it will look something like:
DECLARE @exit INT = 100
, @i INT = 0
, @status BIT = 1;
WHILE (@i < @exit)
BEGIN
SELECT @status = run_status
FROM msdb.dbo.sysjobhistory
WHERE job_id = 'your_job_id';
IF (@status = 0)
BEGIN
RAISERROR('KARAMBA!!!!', 18, 1);
RETURN;
END;
ELSE IF (@status = 1)
BEGIN
SET @i = @exit;
END;
ELSE
BEGIN
WAITFOR DELAY '00:00:01'; -- 1 sec, you might need more
END;
SET @i += 1;
END;
July 6, 2020 at 1:08 pm
Why can't you add the update statement as the first step in the job, and then again as the last step?
Or, remove the code from the job, assuming it's T-SQL, and run it after the update statement?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 6, 2020 at 6:23 pm
You probably need to rethink the approach...
What is the agent job doing? Why does it need to run after this process - and why does it need to run in a loop?
Is the table meant to be some type of queue - where an entry is added so it now gets processed, and the secondary update is to mark the entry as processed?
If you can outline the process you are trying to create, I am sure you can get many options that would not only perform better but also be much more manageable.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply