Setting Irregular Start Date Job Schedule | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Setting Irregular Start Date Job Schedule

Hi Everyone!<br /><br />I have job that run on different date each month. I have "Reset_Schedule_Start_Date" stored procedure on msdb database for this purpose that read a table that has job name and its irregular start date every month.<br /><br />CREATE PROCEDURE Reset_Schedule_Start_Date<br />@job_name VARCHAR(50)<br />AS<br /><br />DECLARE @newdate AS int<br /><br />SET @newdate = (SELECT TOP 1 Start_Date FROM Irregular_Job_Schedule <br /> WHERE job_name = @job_name AND Start_date &gt; convert (char,getdate(),112) <br /> ORDER BY Start_Date)<br /><br />Update sysjobschedules<br />set active_Start_date = @newdate, Enabled = 1, next_run_date= @newdate, next_run_time=active_start_time<br />from sysjobschedules s INNER JOIN sysjobs j ON<br />s.job_id = j.job_id<br />WHERE<br />j.name = @job_name<br />GO<br /><br />Something is missing, because on enterprise manager at the sqlserver agent, after I refresh the job, it does not show next run date and time. I have to manually go to job schedule on enterprise manager- job – job schedule tab- disable the job-click OK- open job schedule again – click checkmark to enable the job – OK – OK again to close the job properties screen – refresh the job – then it show next run date & time.<br /><br />Do you have idea on how to fix this or a better way to automatically schedule irregular date based on the table that stored jobs and their irregular dates through out calendar year? <br /><br />We are on SQLServer 2000, SP3 on Win2000 in 2 nodes Active/Passive Cluster.<br /><br />Thanks! [<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />] [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br /><br />Paulus
First of all refrain updating system tables directly, instead use system supplied stored procedures such as SP_UPDATE_JOB or SP_UPDATE_JOBSCHEDULE in order to define the scheduled time. And on the other part check the job status with SP_HELP_JOB and ensure the next run data is correctly return on Query analyzer, as EM is flaky sometimes showing such misleading information even though the data was updated. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Thanks Satya. I appreciate for the idea. SP_UPDATE_JOBSCHEDULE works for this purpose. I can modify the query to use this sp and read a table that store the next start date for @active_start_date on each job. I should look for SPs first before writing my own. Paulus
Good and books online is the first and best resource for such information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>