CODEWITHSUNDEEP
sqlsql-server
yazanpro
yazanpro

Reputation: 4752

SQL Server Execute Query Every Interval

I would like to execute a query in the Management Studio every 3 seconds for monitoring/maintenance needs. I know I can define a job or use an external application. But I 'm looking for something much simpler. Something like the following pseudo:

DECLARE @Interval INT
SET @Interval = 3000 -- I'm expecting milliseconds
BEGINLOOP (@Interval)
    SELECT * FROM MyTransactions
ENDLOOP

I would like the output to refresh every 3 seconds. Is that possible?

Upvotes: 10

Views: 22728

Answers (3)

Jota Pardo
Jota Pardo

Reputation: 878

I just wanna share other possibility. Avoiding wait for the whole the query ends.

DECLARE @initialTime AS datetime = getdate()
    , @duration AS INT = 15 --in seconds
    , @interval AS INT = 3 --in seconds
    , @amountOfCycles AS INT = 0

DECLARE @maxTime as datetime = dateadd(second,@duration,@initialTime)

WHILE @initialTime <= @maxTime
BEGIN
    --WAITFOR DELAY '00:00:01'
    SET @amountOfCycles += 1

    IF DATEDIFF(second,@initialTime,GETDATE()) >= @interval 
    BEGIN 
        print 'do something'
        SET @initialTime = GETDATE()
    END
    
END

PRINT '@amountOfCycles: ' + ISNULL(CONVERT(VARCHAR,@amountOfCycles),'')

I omitted the WAITFOR DELAY command (inside WHILE). However, I noticed the number of cycles for my example of 15 seconds doing the process, it took almost 2 millions times in the cycle... too much memory.

@amountOfCycles: 1798079

It should be taken into account, but it is another possibility.

However, for now, it solves @yazanpro's doubt

Upvotes: 1

user7485046
user7485046

Reputation: 1

DECLARE @i INT = 1;

WHILE (@i <= 60)
 BEGIN
  WAITFOR DELAY '00:00:05'

       /*Your Script*/

 SET  @i = @i + 1;
END 
print 'completed'

Upvotes: -1

Randy Minder
Randy Minder

Reputation: 48392

You could use WAITFOR in a loop. You would specify the interval time in the WAITFOR statement.

Something like this:

WHILE 1=1
BEGIN
   WAITFOR DELAY '00:00:05' -- Wait 5 seconds

   SELECT * FROM MyTransactions

   -- Break on some condition
END

Upvotes: 12

Related Questions