Scedule and execute job from another job as TSQL proc

Question

Hi,
I want to execute the specific job"OnDemand_Goals" at 8am EST on the day 4 of every quarter like mentioned in the below condition.I am executing the SP below from another job"Scheduled_Goals".Can you let me know:

how many times on Day4 at 8th HR (jobs run in the server in EST already) would the job run as i am not specifying mm,ss conditons? I am unsure if the job would run more than once with my query.I want it to run only once.
Do still need to add a schedule in the job"Scheduled_Goals" ?
I tried this in my test machine and job didnt kick off at all.Can you pls correct

alter proc Load_QTRMilestones as
begin
if
(
datepart(day,convert(date,DATEADD(QUARTER, DATEDIFF(QUARTER, 0,getdate()), 3)))=4 and
(datepart(hour,convert(date,DATEADD(QUARTER, DATEDIFF(QUARTER, 0,getdate()), 3)))=8 and
DATEPART(MONTH, getdate()) IN (1,4,7,10))
)
begin
exec msdb.dbo.sp_start_job N'OnDemand_Goals'
END
end

Pls provide another solution you know would work better.

Accepted Answer

I would directly add a schedule to run the job "OnDemand_Goals" at 8:00 AM on the fourth day of the month. And then add a check task as the first step using the following query.

IF MONTH(GETDATE()) NOT IN (1, 4, 7, 10)
BEGIN
 RAISERROR (15600, -1, -1, 'Not in January, April, July or October!');
END

Set "On failure action" = "Quit the job reporting success".

Answer

I think you need a table to hold whether you have executed the job or not for a specific quarter. The reason for this is that the server may be down at the intended starting point, and if so you will miss the boat and the job will not run. (I think that is the way Agent runs, but I am fully sure, so you may want to test.)

The table would hold the scheduled starting times, that is 2020-10-04 08:00, 2021-01-04 08:00 etc. together with flag whether the job has not started, is running or has completed (which needs to be set by the job itself.)

Your monitoring job would run with some frequency, and check if you are past the starting time for the next run.

Answer

Hi @PV ,
If you do not confirm whether the job is running, you can check whether the job is running successfully according to the following code

    -- Query the execution status of the enabled job  
    select  b.name,   a.step_name, msdb.dbo.agent_datetime( run_date, run_time) AS 'RunDateTime' ,    
            a.run_duration,    
             case  when a.run_status=0 then 'Failed'    
             when a.run_status= 1 then 'Succeeded'  
             when a.run_status= 2 then 'Retry'  
             when a.run_status= 3 then 'Canceled'  
             else 'Unknown'    
             end as run_status,    
           a.[message]    
    from msdb .dbo. sysjobhistory a inner join msdb .dbo. sysjobs b    on a.job_id =b .job_id   
    inner join msdb. dbo.sysjobsteps s on a .job_id = s .job_id and a.step_id = s .step_id  
    where b .enabled = 1

Best Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

Scedule and execute job from another job as TSQL proc

2 additional answers

Your answer