CODEWITHSUNDEEP
sql-server
Ian Ringrose
Ian Ringrose

Reputation: 51917

Can I get SQL Server to call a stored proc every n seconds?

I wish to have a stored proc that is called every n seconds, is there a way to do this in SQL Server without depending on a separate process?

Upvotes: 15

Views: 17973

Answers (5)

Tahir Rehman
Tahir Rehman

Reputation: 332

WAITFOR   
{  
    DELAY 'time_to_pass'   
  | TIME 'time_to_execute'   
  | [ ( receive_statement ) | ( get_conversation_group_statement ) ]   
    [ , TIMEOUT timeout ]  
}

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294287

Use a timer and activation. No external process, continues to work after a clustering or mirroring failover, continues to work even after a restore on a different machine, and it works on Express too.

-- create a table to store the results of some dummy procedure
create table Activity (
    InvokeTime datetime not null default getdate()
    , data float not null);
go  

-- create a dummy procedure
create procedure createSomeActivity
as
begin
    insert into Activity (data) values (rand());
end
go

-- set up the queue for activation
create queue Timers;
create service Timers on queue Timers ([DEFAULT]);
go

-- the activated procedure
create procedure ActivatedTimers
as
begin
declare @mt sysname, @h uniqueidentifier;
begin transaction;
    receive top (1)
        @mt = message_type_name
        , @h = conversation_handle
        from Timers;

    if @@rowcount = 0
    begin
        commit transaction;
        return;
    end

    if @mt in (N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
        , N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
    begin
        end conversation @h;
    end
    else if @mt = N'http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer'
    begin
        exec createSomeActivity;
        -- set a new timer after 2s
        begin conversation timer (@h) timeout = 2;
    end
commit
end
go

-- attach the activated procedure to the queue
alter queue Timers with activation (
    status = on
    , max_queue_readers = 1
    , execute as owner
    , procedure_name = ActivatedTimers);
go  


-- seed a conversation to start activating every 2s
declare @h uniqueidentifier;
begin dialog conversation @h
    from service [Timers]
    to service N'Timers', N'current database'
    with encryption = off;
begin conversation timer (@h) timeout = 1;

-- wait 15 seconds
waitfor delay '00:00:15';

-- end the conversation, will stop activating
end conversation @h;
go

-- check that the procedure executed
select * from Activity;

Upvotes: 21

SQLSharma
SQLSharma

Reputation: 1

If you want to keep a SSMS query window open:

While 1=1
Begin
exec "Procedure name here" ;
waitfor delay '00:00:15';
End

Upvotes: -2

Philip Kelley
Philip Kelley

Reputation: 40319

I once set up a stored procedure that ran continuously, uisng a loop with a WAITFOR at the end of it.

  • The WHILE condition depended upon the value read from a simple configuration table. If the value got set to 0, the loop would be exited and the procedure finished.
  • I put a WAITFOR DELAY at the end, so that however long it took to process a given iteration, it would wait XX seconds until it ran it again. (XX was also set in and read from the configuration table.)
  • If it must run at precise intervales (say, 0, 15, 30, and 45 seconds in the minute), you could calculate the appropriate WATIFOR TIME value at the end of the loop.
  • Lastly, I had the procedure called by a SQL Agent job once a minute. The job would always be "running" showing that the procedure was running. If the procedure was killed or crashed, the job would start it up in no more than 1 minute. If the procedure was "turned off", the procedure still gets run but the WHILE loop containing the processing does not get entered making the overhead nill.

I didn't much like having it in my database, but it fulfilled the business requirements.

Upvotes: 2

marc_s
marc_s

Reputation: 754488

You can set up a SQL Agent job - that's probably the only way to go.

SQL Server Agent is a component of SQL Server - not available in the Express editions, however - which allows you to automate certain tasks, like database maintenance etc. but you can also use it to call stored procs every n seconds.

Upvotes: 6

Related Questions