Scheduling a job to run every 30 seconds... -- sql server notification services

Roz
6/21/2007 1:11:00 PM

Adam,

I will try your suggestion. Though, it looks like it'll work for me.

Thanks so much.

Roz

[quoted text, click to view]

Adam Machanic
6/21/2007 2:35:17 PM

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

[quoted text, click to view]

Adam Machanic
6/21/2007 2:41:04 PM

Oops, that should have been @i >= 0 in both loops, rather than @i > 0.

--

Adam Machanic
SQL Server MVP

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

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server notification services

sql server notification services : Scheduling a job to run every 30 seconds...