sql 存储过程定时执行方法代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】 sql 存储过程定时执行方法
作者:Dezai.CN / 发布于2011/12/13/ 797
<div><span style="color: #008080">--</span><span style="color: #008080"> 创建存储过程，每隔 30 秒钟运行一次。</span><span style="color: #008080">
</span><span style="color: #0000ff">create</span><span style="color: #000000"> </span><span style="color: #0000ff">procedure</span><span style="color: #000000"> usp_daemon
</span><span style="color: #0000ff">as</span><span style="color: #000000">
</span><span style="color: #0000ff">set</span><span style="color: #000000"> nocount </span><span style="color: #0000ff">on</span><span style="color: #000000">
</span><span style="color: #0000ff">while</span><span style="color: #000000"> </span><span style="color: #800000; font-weight: bold">1</span><span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">
</span><span style="color: #0000ff">begin</span><span style="color: #000000">
</span><span style="color: #0000ff">update</span><span style="color: #000000"> </span><span style="color: #ff0000">[</span><span style="color: #ff0000">user</span><span style="color: #ff0000">]</span><span style="color: #000000"> </span><span style="color: #0000ff">set</span><span style="color: #000000"> type</span><span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0</span><span style="color: #000000"> </span><span style="color: #0000ff">where</span><span style="color: #000000"> data</span><span style="color: #808080"><</span><span style="color: #ff00ff">getdate</span><span style="color: #000000">();
</span><span style="color: #0000ff">delete</span><span style="color: #000000"> </span><span style="color: #0000ff">from</span><span style="color: #000000"> </span><span style="color: #ff0000">[</span><span style="color: #ff0000">tab</span><span style="color: #ff0000">]</span><span style="color: #000000"> </span><span style="color: #0000ff">where</span><span style="color: #000000"> userid </span><span style="color: #808080">in</span><span style="color: #000000"> (</span><span style="color: #0000ff">select</span><span style="color: #000000"> userid </span><span style="color: #0000ff">from</span><span style="color: #000000"> </span><span style="color: #ff0000">[</span><span style="color: #ff0000">user</span><span style="color: #ff0000">]</span><span style="color: #000000"> </span><span style="color: #0000ff">where</span><span style="color: #000000"> type</span><span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0</span><span style="color: #000000">);
</span><span style="color: #0000ff">waitfor</span><span style="color: #000000"> delay </span><span style="color: #ff0000">'</span><span style="color: #ff0000">00:00:30</span><span style="color: #ff0000">'</span><span style="color: #000000">
</span><span style="color: #0000ff">end</span><span style="color: #000000">
</span><span style="color: #0000ff">go</span><span style="color: #000000">

</span><span style="color: #008080">--</span><span style="color: #008080"> 将 sql server 实例设置为&ldquo;在实例启动时扫描可自动执行的存储过程&rdquo;</span><span style="color: #008080">
</span><span style="color: #000000">sp_configure </span><span style="color: #ff0000">'</span><span style="color: #ff0000">show advanced options</span><span style="color: #ff0000">'</span><span style="color: #000000">,</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">
</span><span style="color: #0000ff">reconfigure</span><span style="color: #000000">
sp_configure </span><span style="color: #ff0000">'</span><span style="color: #ff0000">scan for startup procs</span><span style="color: #ff0000">'</span><span style="color: #000000">,</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">
</span><span style="color: #0000ff">reconfigure</span><span style="color: #000000">
sp_configure </span><span style="color: #ff0000">'</span><span style="color: #ff0000">show advanced options</span><span style="color: #ff0000">'</span><span style="color: #000000">,</span><span style="color: #800000; font-weight: bold">0</span><span style="color: #000000">
</span><span style="color: #0000ff">reconfigure</span><span style="color: #000000">

</span><span style="color: #008080">--</span><span style="color: #008080"> 将存储过程设置为&ldquo;实例启动时自动执行&rdquo;</span><span style="color: #008080">
</span><span style="color: #000000">sp_procoption </span><span style="color: #ff0000">'</span><span style="color: #ff0000">usp_daemon</span><span style="color: #ff0000">'</span><span style="color: #000000">,</span><span style="color: #ff0000">'</span><span style="color: #ff0000">startup</span><span style="color: #ff0000">'</span><span style="color: #000000">,</span><span style="color: #ff0000">'</span><span style="color: #ff0000">true</span><span style="color: #ff0000">'</span><span style="color: #000000">
</span></div>
评论列表
本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。
试试其它关键字
　存储过程　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)