Categories
Sql Server

Run a SQL Server Job on Conditions of Different Job

Run a SQL Server Job on Conditions of Different Job

Hi Friends yesterday i got a mail from my project manager, regarding a job. Which is scheduled for 6:00 PM on daily basis. But he want to implement some conditions for it.

Scenario: Actually I have configured a job in SQL Server which is executed by user itself through C# code. In C# we have written code to run it when user want and a same job is scheduled for daily on 6:00 PM. Now my manager wants that scheduled job will not be executed if following conditions does not meets:

  1. If manual job successful executed between 5:00-6:00 PM then scheduled job will not execute.
  2. If manual job executed before 5:00 pm then scheduled job will execute for the day.
  3. If job executed between 5:00-6:00 pm but forcefully stopped then scheduled job will execute.

Solution: Here what I have implemented is following:

  1. I have created a new job in SQL Server, i.e. for scheduled job. Means a separate job which is runned by user through C# code.
  2. I scheduled it for 6:00 PM daily basis.
  3. Now main point comes, on T SQL query i do some manipulation to check Manual job(run by user through C# code) between 5:00 – 6:00 PM by using JOB ID, Job Status for the current date. If so then job do nothing else it will run our First Job(Manual One).

Query:

----- USE DATABASE MSDB ----
USE MSDB
GO
 
----- CHECK IF JOB IS RUNNED FOR CURRENT DATE -----
----- JOB ID, TIME, CURRENT DATE, AND RUN STATUS FOR JOB HAS BEEN CHECKED IN BELOW QUERY------

IF(SELECT COUNT(*) FROM SYSJOBHISTORY WHERE RUN_DATE=CONVERT(INT, CONVERT(VARCHAR(10), GETDATE(), 112))
and run_time between 170000 and 180000 AND STEP_NAME='(JOB OUTCOME)'
AND JOB_ID='<JOB ID COMES HERE>' AND RUN_STATUS=1) >0
 
----- IF JOB IS RUN FOR CURRENT DATE/ DO NOTHING -----
PRINT 'TASK IS COMPLETED'
ELSE
 
----- ELSE RUN FOLLOWING JOB ------
EXEC DBO.SP_START_JOB N'MANUAL_JOB'