Schedule monthly job

I have a need to set up an odd schedule.

I want to run a job ever saturday of the month EXCEPT the 3rd saturday (where I need to run the job on Sunday).

So I set up 4 jobs -- using monthly frequency I selected the first Sat, the second Sat, the third Sun, and the forth Sat.

Sounds good. Right? Not quite. Look at a calendar for September 2006... it has FIVE saturday's. There is no option for the "fifth" of a day and if I use the "last" day I suspect that my job will run twice when there are only four instances of a day.

Any ideas?

Jason,

Thanks for your feedback. SQL Agent Scheduling options available in SQL 2005 may not meet your specific need. Could you please log this issue as a feature request through Connect web site so that our product team could take a look at your feature request?

https://connect.microsoft.com/SQLServer/Feedback

Click on Submit Feedback

You could try a different approach. You could create T-SQL job step add the first job step in your job step sequence and let the T-SQL code determine if it was the 3rd saturday

1) Create a monthly schedule that runs every Saturday and Sunday

2) Let the first job step be a T-SQL job step that executes T-SQL code to determine

a) if current date was a third saturday and issue a RAISEERROR

b) if current date was not a fourth Sunday then issue a RAISERROR

Example t-sql code:

declare @.tmpdate datetime

SET @.tmpdate = '09/10/2006' --GETDATE()

declare @.firstday_in_month datetime

SET @.firstday_in_month = DATEADD(DD, 1 - DATEPART(dd, @.tmpdate), @.tmpdate)

declare @.week_for_firstday_in_month int

SET @.week_for_firstday_in_month = DATEPART(wk, @.firstday_in_month)

declare @.week_for_currentday int

SET @.week_for_currentday = DATEPART(wk, @.tmpdate)

declare @.week_in_this_month int

SET @.week_in_this_month = @.week_for_currentday - @.week_for_firstday_in_month + 1

-- check if date is 3rd saturday in this month

IF(DATEPART(dw, @.tmpdate) = 7) AND ( @.week_in_this_month = 3)

BEGIN

SELECT @.tmpdate, 'Saturday', @.week_in_this_month

RAISERROR (N'Third Saturday', 16,1)

END

-- check if date is not the 4th Sunday in this month

IF(DATEPART(dw, @.tmpdate) = 1) AND ( @.week_in_this_month <> 4)

BEGIN

SELECT @.tmpdate, 'Sunday', @.week_in_this_month

RAISERROR (N'Not a Third Sunday', 16,1)

END

3) In the job step defined in step 2, In Advanced Tab, "On Failure Action", select "Quit the job reporting success"

4) In Job step defined in step 2, In Advanced Tab, "On Success Action", select "Goto next step"

5) Add all other job steps specific to this job after the first job step

Thanks
Sethu Srinivasan, Software Design Engineer, SQL Server Manageability
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

|||

Excellent workaround Sethu. Thanks.

And I will submit it as an enhancement.

|||

Sethu,

I have a similar schedule issue. I need to run a job every month on mondays after the 1st friday of the month. How do I accomplish this. Let meknow

|||

I created a simple script using VBScript with a combination of TSQL to check whether I should run a weekly or monthly job. Here's a sample syntax I used

Dim objShell

SET objShell = CreateObject("Wscript.Shell")

'change this value to any number between 1 to 7 being Sunday to Saturday

If Weekday(Date)=1 Then
If DatePart("m",(DateAdd("d",7,Date()))) <> DatePart("m",Date()) Then
'Run this batch file if it is NOT the last Sunday of the month
objShell.Run("C:\weekly_backup.bat")
Else
'Run this batch file if it is the LAST Sunday of the month
objShell.Run("C:\monthly_backup.bat")
End If
Else
'Run this batch file if it is a weekday
objShell.Run("C:\daily_backup.bat")

End if

SET objShell=NOTHING

My batch files are simply calls to either osql or sqlcmd which does a full backup