Scheduling a job to run every 30 seconds...

Hello, all. I need to schedule a SQL job to run every 30 seconds. However,
it looks like the shortest frequency SQL will allow is every minute. Is what
I want to do possible?

Thnx in advance,

Roz

I don't believe you can go below one minute, but you can work around it
easily enough. In your job, set up a loop:


DECLARE @i INT
SET @i = 1
WHILE @i > 0
BEGIN
/*
do whatever work you need to do, here
*/

WAITFOR DELAY '00:00:30'

SET @i = @i - 1
END


You might have to tweak the WAITFOR interval a bit, depending on how
long the work actually takes to do. But if you wanted to get a bit
fancier--and more exact--with it, you could do something like:


DECLARE @startTime DATETIME
SET @startTime = GETDATE()

DECLARE @i INT
SET @i = 1
WHILE @i > 0
BEGIN
/*
do whatever work you need to do, here
*/

WHILE DATEDIFF(second, @startTime, GETDATE()) < 30
WAITFOR DELAY '00:00:01'

SET @i = @i - 1
END


--

Adam Machanic
SQL Server MVP

Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220



"Roz" wrote in message

> Hello, all. I need to schedule a SQL job to run every 30 seconds.
> However,
> it looks like the shortest frequency SQL will allow is every minute. Is
> what
> I want to do possible?
>
> Thnx in advance,
>
> Roz