sql-server – 可以让SQL Server每n秒钟调用一个存储过程吗？

发布时间：2020-12-12 16:46:23 所属栏目：MsSql教程来源：网络整理

导读：我希望有一个每n秒调用一个存储过程,有没有办法在SQL Server中执行,而不依赖于单独的进程？解决方法使用 timer和 activation.在集群或镜像故障切换后,外部进程继续工作,即使在不同机器上还原后仍能继续运行,而且它也适用于Express. -- create a table to st

我希望有一个每n秒调用一个存储过程,有没有办法在SQL Server中执行,而不依赖于单独的进程？

解决方法

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!