April 27, 2006 at 5:02 pm
Hi there,
I was trying to get a Scheduled Job (in SQL Server 2000) to run every second or two. I had three T-Sql blocks I needed to execute every two seconds, and you know you can just schedule recurring times to every minute.
I started out by introducing multiple Minute recurring schedules starting at different second offsets. This is okay unless you really need something more frequent (like I did, and I didn't want to add 30 different schedules by hand).
Then I thought more clearly, all I wanted was to start a job that never stoped, and, to avoid overflowing the server with executions of my 3 T-Sql blocks, do some sleeping meanwhile.
The first part is done by setting the last step to jump to the 1rst step On Success.
The last, and also easy, part is adding a Step to sleep for a period of time, I used a JScript (ActiveX Script) with the following code (for the original go to http://www.webdeveloper.com/forum/archive/index.php/t-40332.html):
Date.ONE_SECOND = 1000
function nap (m) {var then = new Date(new Date().getTime() + m); while (new Date() < then) {}}
nap(
And there the process goes. To see how well it's going check the Job History and select the checkbox "Show step details".
April 28, 2006 at 3:34 am
You might want to search the forum for SQL Server Job Scheduling. I remember someone posting about setting a schdule to run at fractions of a minute (editing a value in msdb..sysjobschedules)
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:
April 28, 2006 at 5:29 am
Thanks, a native solution in SQL Server can be found at http://www.sqlservercentral.com/columnists/phe/sqlserverjobscheduling.asp, topic Schedules for frequent executing jobs.
Basically you can change the frequency type from Minutes (4) or Hours (8) to Seconds (2), use the following script:
UPDATE sysjobschedules
SET
freq_subday_type = 2
FROM sysjobs j INNER JOIN
sysjobschedules js ON j.job_id = js.job_id
WHERE (j.name LIKE '<Your job name>')
But in SQL Server 2000 SP3 I can't get the Job running after changing these settings. So I'm using the sleep command version (see my previous post) which provided the best results.
April 28, 2006 at 5:43 am
I have used the various frequency types as you've found- I usually have set these using SQLDMO.. and I have also gone the route you were thinking originally - where I wanted to schedule the job to kick off at 9 AM for example and then stay running for 8 hours - and then while running it loops through and performs some execution(s) every 30 seconds or whatever..
For my execution statements I created a stored procedure but I sure you could just execute T-SQL statements directly as well.. the section below was stored in the command section of the T-SQL type jobstep
--------------------------------------------------------------------------------------------------------------
--this ran my stored procedure over a period of time every 30 seconds
declare @BeginTime DATETIME
declare @EndTime DATETIME
declare @interval INT
SET @BeginTime = GETDATE()
SET @ENDTIME = DATEADD(hh, 8, @BeginTime) -- the 8 says to run job for 8 hours
WHILE 1 = 1
BEGIN
WAITFOR DELAY '00:00:30' --specify your 'seconds to delay/wait for loop
EXEC --stored procedure location..name (could execute t-sql here I think)
select @interval = DATEDIFF(ss,@EndTime,GETDATE())
IF @interval > 0
BREAK
ELSE
CONTINUE
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply