Update table and then executeJob in loop

  • 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

  • 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.

  • 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

     

  • 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;

    • This reply was modified 1 year, 10 months ago by  dkultasev.
  • 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/

  • 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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    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