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:
- If manual job successful executed between 5:00-6:00 PM then scheduled job will not execute.
- If manual job executed before 5:00 pm then scheduled job will execute for the day.
- 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:
- 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.
- I scheduled it for 6:00 PM daily basis.
- 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).
----- 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'