I have a SQL job that I have been trying to configure to run between a certain time (4pm - 7pm). I created the job I want to run between this time (lets call it DynJob). I set it a default schedule with the days I want it to run, and a default time value of 4pm.
But I don't want it to run at 4pm every single time it runs. I want it to run between 4pm - 7pm.
So I created another job (SJob). This job runs at 2pm (2 hours earlier than the scheduled start time for DynJob). All SJob does is run a query against the dbo.sysjobschedules, and does some math to come up with a value somewhere between 160000 and 190000, and inserts that value into the next_run_time field where the schedule_id = DynJob's schedule ID.
And it works (not really). It will adjust the time in the table, and I can see it gets a value (lets say 175691). But, it's all for not. Because when 4pm comes around, DynJob still gets run. Then SQL sets DynJobs next_run_time back to 4pm (160000) after it has run.
Am I going at this all wrong? Is there an easier way to set a job schedule for a window of time instead of one static time?
Here is the TSQL I am using that I talked about above.
USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DECLARE @CallTime int = 1
DECLARE @MaxTime int = 30000
SET @CallTime = @MaxTime*RAND() + 160000
UPDATE dbo.sysjobschedules
SET next_run_time = @CallTime
WHERE schedule_id = 1
As always, thanks for taking the time!
P.S. Yes, I know having 2 jobs and 2 schedules just to get this to work is silly, but I am a newb and know not the ways of SQL Server Agent :) So any answers, or helpful documentation is always appreciated! If this is dumb, tell me! But explain it as well
Aucun commentaire:
Enregistrer un commentaire