Automatic Scheduling of irregular job start date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Automatic Scheduling of irregular job start date

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-29 : 11:29:43

Paulus writes "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.

CREATE PROCEDURE Reset_Schedule_Start_Date
@job_name VARCHAR(50)
AS

DECLARE @newdate AS int

SET @newdate = (SELECT TOP 1 Start_Date FROM Irregular_Job_Schedule
WHERE job_name = @job_name AND Start_date > convert (char,getdate(),112)
ORDER BY Start_Date)

Update sysjobschedules
set active_Start_date = @newdate, Enabled = 1, next_run_date= @newdate, next_run_time=active_start_time
from sysjobschedules s INNER JOIN sysjobs j ON
s.job_id = j.job_id
WHERE
j.name = @job_name
GO

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.

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?

We are on SQLServer 2000, SP3 on Win2000 in 2 nodes Active/Passive Cluster.

Thanks!
Paulus"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-29 : 12:19:58

You should not be updating sysjobschedules directly. You should be using sp_update_jobschedule to update it. It will notify the SQLAgent to modify those columns.

Whenever a system stored procedure is available to modify the system tables, you should use it instead of directly modifying them.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources