July 2, 2009 at 9:08 am
Running SQL Server 2005 Enterprise Edition SP2 CU 10 -
I'm trying to find a way to have a job update a single field in a single table every July 1st. My SQL is pretty simple -
UPDATE TABLEA SET FIELDB = XXXXX
When I try to set up the job thru the GUI - it looks like I can have it occur Monthly on Day 1 of every 12 months occuring once at 12:00 AM with a Start Date of July 1, 2009 and no End Date. When I look in the msdb..sysjobschedules it shows the next_run_date of 20090801 with a next_run_time of 0. Does this mean it will fire on August 1st and do the update? That is not what I obviously want. It should update the table on July 1, 2010, 2011, etc.
Just to play it safe - maybe I should change my SQL to be something like this -
IF datepart(mm,getdate()) = 7 and datepart(dd,getdate()) = 1
UPDATE TABLEA SET FIELDB = XXXXX
ELSE
END
and change the job schedule to run Day 1 of every 1 month occuring at 12:00 AM
Any problems with the above SQL and monthly schedule? Or do you think the yearly schedule as described above will work and not kick off on August 1st?
Thank you for any feedback.
July 2, 2009 at 9:34 am
if the start date is in the past then your schedule will pick up the first day of the next month because the first day of current month has past. If you change your schedule start date to be 2010/07/01 then the schedule should run as you want it to.
July 2, 2009 at 9:44 am
The reason you don't see the next run date is because sysjobschedules refreshes every 20 minutes from the sysschedules system table. The schedule you set will execute the job only on July 1 of each year.
Note: it's already July 2 here so I tested this by setting an annual schedule that starts at 12:00 AM on July 3, 2009.
Greg
July 2, 2009 at 10:42 am
Thank you both. I will set the start date into the future and it should work as expected.
I appreciate the prompt replies. Thanks again.
July 2, 2009 at 4:49 pm
Aw, I messed that one up. I thought you were seeing 0 for next_run_date. I should have just pointed out that the date you set it for was already in the past like Bob did.
Greg
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply