How to run a SQL Server Agent job step on certain days/frequency

_{Friday, January 10, 2014 6:21 PM}

I've a SQL Server Agent job. One of the requirement is that a particular step should only run on certain frequency i.e. Certain Date and time. Any suggestions how can that be done?

GBM

_{Friday, January 10, 2014 6:32 PM ✅Answered | 2 votes}

yes

1. Make first step as a Transact SQL step. Keep command as below

IF DAY(GETDATE()) = <Your date value>
AND DATEPART(hh,GETDATE()) = <your time hour)
AND DATEPART(minute,GETDATE()) = <your time minute)
SELECT 1
ELSE 
RAISERROR 'Job cant be run at this time'

Then for job step properties set the following properties in advanced tab

This would make sure it will quit the job without executing other core steps untill it reaches the required day and time

You can add more conditions in IF based on requirement like if you want time to be on certain second or month to be particular value etc

Please Mark This As Answer if it helps to solve the issue Visakh http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

_{Friday, January 10, 2014 6:28 PM}

Please see:

http://technet.microsoft.com/en-us/library/cc645912.aspx

Pick "Run Once" and put in your specific date and time.

_{Friday, January 10, 2014 7:16 PM}

Hi Visakh, this a great idea. Thanks!

When I tried your suggestion but my step didn't run as per your suggestion. So I changed it to the following:
IF ( DATENAME(dw, GETDATE()) = 'Friday'
     AND DATEPART(hh, GETDATE()) = 10
     AND DATEPART(minute, GETDATE()) = 56
   )
    SELECT
            1
ELSE
    RAISERROR ('Job cant be run at this time', -- Message text.
               16, -- Severity
               1 -- State.
               );

but still no luck. Can you please suggest what could be wrong with this sql?

GBM

_{Friday, January 10, 2014 7:41 PM}

The idea should work fine...You need to change the success and failure action accordingly.

consider there are three steps Step1,Step2 and Step3 in your job.

If step1 = right date and time then step2 else step3

-Prashanth