Hi, I'm trying to implement the following scenario for an SQL 2008 job: **Step 1.** Checks if the incoming data files have been received. If not, the step fails, the job is countinued at the Step 4. **Step 2.** Files are processed. **Step 3.** The Job is rescheduled to start at the same time next day. The job ends here reporting success. **Step 4.** Reminders are sent to the users who were supposed to supply incoming files. The job is scheduled to run every 30 minutes every day starting at 11 AM. The process in the Step 2 requires all the files to be received. The users routinely forget or run late sending files, that is why we have to run the job as many times as it takes until we get all the files. The problem is that the job keeps running on the old schedule even after it has been changed. The schedule IS changed - I can see its new start date in the "Schedules" tab of the job's Properies; I can see it in the sysschedules table as well. In the Job Activity Monitor, however, it shows that the next run is still in the next 30 minutes TODAY. I added a line to the code, to update sysjobschedules table. It shows the schedule's start date TOMORROW. But it does not affect the Job Activity Monitor, and the job, as a matter of fact, runs accordingly to it, and not to the system tables where its porperties are saved. What does this mean? What and where else the schedule's properties are saved that I cannot reach to them? Here is the code in the Step 3: DECLARE @SCHED int, @JOB nvarchar(200), @start_date int, @run_time int = 110000 SELECT TOP 1 @JOB = job_id FROM msdb.dbo.sysjobs WHERE [name] = 'MyJob' SET @start_date = CONVERT(int, CONVERT(varchar(8), DATEADD(dd, 1, getdate()), 112)) SELECT TOP 1 @SCHED = schedule_id FROM msdb.dbo.sysjobschedules WHERE job_id = @JOB IF @SCHED IS NOT NULL BEGIN EXEC msdb.dbo.sp_detach_schedule @job_id = @JOB, @schedule_id = @SCHED, delete_unused_schedule=0 EXEC msdb.dbo.sp_update_schedule @schedule_id = @SCHED, @active_start_date = @start_date, @active_start_time = @run_time EXEC msdb.dbo.sp_attach_schedule @job_id = @JOB, @schedule_id = @SCHED END ELSE BEGIN SELECT @start_date EXEC msdb.dbo.sp_add_jobschedule @job_id = @JOB, @name =N'Schedule1', @enabled=1, @freq_type=8, @freq_interval=62, @freq_subday_type=4, @freq_subday_interval=30, @freq_relative_interval=0, @freq_recurrence_factor=1, @active_start_date = @start_date, @active_end_date= 99991231, @active_start_time = @run_time, @active_end_time=235959 END UPDATE msdb.dbo.sysjobschedules SET next_run_date = @start_date, next_run_time = @run_time WHERE job_id = @JOB
Can you please post the output of the following script SELECT s.schedule_id , [SJ].[name] JobName , [s].name ScheduleName , S.enabled , freq_type , freq_interval , freq_subday_type , freq_subday_interval , active_start_date , active_end_date , active_start_time , active_end_time , js.next_run_date , js.next_run_time FROM msdb.dbo.[sysjobs] AS SJ JOIN msdb.dbo.sysjobschedules AS js ON [SJ].[job_id] = [js].[job_id] JOIN msdb.dbo.sysschedules AS s ON js.schedule_id = s.schedule_id WHERE [SJ].[name] = 'MyJob' Moreover, looking at the situation, if I understand correctly, a simpler workflow could be - Add two schedules to the Job. one for daily at 11 A.M. and second for every 30 minutes with default status disabled for the latter. - Check if the files exist, process the files and disable the every 30 minutes schedule - If the files does not exist, enable the every 30 minutes schedule Working on this kind of pattern, I believe it would be much easier.