asked
Problem update a job schedule programmatically
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.
Besides, looking at your manual update of schedule table, you need to refresh the job cache manually to make it work i.e. EXEC msdb.dbo.sp_sqlagent_notify @op_type = N’J’, @job_id = @JOB, @action_type = N’U’ and if there is multi-server environment, then to inform the TSX server, you may also need to execute the following depending upon the environment EXECUTE @retval = sp_post_msx_operation @operation = 'INSERT', @object_type = 'SCHEDULE', @schedule_uid = @schedule_uid --UID not ID Furthermore, you can try a refresh of the job cache anyways and see if that helps.
@Usman, thank you so much for your contribution! I do agree that the two-schedule scenario that you suggested looks like a better solution, and I'll go ahead and try it. I see, as well, what you mean by suggesting to run sp_sqlagent_notify manually after updating sysjobschedules manually. I'll try this, too. I'll be back with my results. Thanks a lot!
I think I'll have to go for the two-schedule scenario suggested by @Usman, because my "change schedule programmatically" strategy still does not work... I'd like, however, to understand what's "wrong" with the Job Activity Monitor. It kind of rules the game. After I ran the script to change the schedule (including msdb.dbo.sp_sqlagent_notify), the fields "active_start_time" and "active_start_day" show correct values: 11 AM of the next day. The Job Activity Monitor, however, still shows that the next run will occur in 30 minutes today. And it does occur! After which the values in the "active_start_time" and "active_start_day" fields are changed to show the next run in 30 minutes today - as it is now in the Job Activity Monitor.
If you could have posted the output of the script (I asked for), it could have lead to the root cause. I suspect there are more than one schedules currently attached to the job. So even after changing one, the other still executes the job. But this can only be verified by yourself or through the output.
@Usman, here is the output, below. There ARE 2 schedules now, after I added the second one on your advice (#33; it is disabled now), but before that there was just one schedule (on row in the output). And of course I could have seen in several places that there was only one schedule. Thank you for the follow-up. I'm about to give up on the changing the schedule programmatically. I guess you are right, other workarounds, like redirecting steps, are easier, and at least they work :) =========================== schedule_id 31 33 ScheduleName Schedule1 Schedule2_OnceAt11 enabled 1 0 freq_type 8 8 freq_interval 62 63 freq_subday_type 4 1 freq_subday_interval 30 0 active_start_date 20120331 20120330 active_end_date 99991231 99991231 active_start_time 100000 100000 active_end_time 175959 235959 next_run_date 20120402 20120331 next_run_time 100000 100000
4 People are following this question.
