- 我可以让SQL Server每秒调用一次存储过程?

140 2

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

时间: 原作者:

54 1

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



原作者:
125 3

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

SQL Server代理是SQL Server的一个组件 - ,但是,在Express版本中不可用 - 它允许你自动执行某些任务,如数据库维护等,但是,你也可以用n来每隔n秒调用一次存储过程。

原作者:
97 1

WAITFOR 


{ 


 DELAY 'time_to_pass' 


 | TIME 'time_to_execute' 


 | [ ( receive_statement ) | ( get_conversation_group_statement ) ] 


 [ , TIMEOUT timeout ] 


} 



原作者:
115 4

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


While 1=1


Begin


exec"Procedure name here" ;


waitfor delay '00:00:15';


End



原作者:
...