我可以让SQL Server每隔n秒调用一个存储过程吗?

使用 timer 和 activation . 没有外部进程,在群集或镜像故障转移后继续工作,即使在其他计算机上恢复后也继续工作,而且它也在Express上工作。

-- 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;

您可以设置一个SQL代理作业-这可能是唯一的方法。

SQL Server Agent

我曾经设置了一个连续运行的存储过程,在循环的末尾使用WAITFOR。

• WHILE条件取决于从简单配置表中读取的值。如果值设置为0,则退出循环并完成该过程。
• 如果它必须以精确的间隔运行(例如,0、15、30和45秒),则可以在循环结束时计算相应的瓦特弗时间值。
• 最后,我让SQL代理作业每分钟调用一次过程。作业将始终“运行”以显示过程正在运行。如果程序被终止或崩溃,作业将在不超过1分钟内启动。如果该过程被“关闭”,则该过程仍将运行,但包含处理的WHILE循环不会被输入,从而使开销为零。

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

如果要保持SSMS查询窗口打开:

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