如何在 SQL Server Express Edition 中每天运行一个存储过程？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1675206/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to run a stored procedure every day in SQL Server Express Edition?
提问by Thomas Bratt
How is it possible to run a stored procedure at a particular time every day in SQL Server Express Edition?
在 SQL Server Express Edition 中如何每天在特定时间运行存储过程？
Notes:
笔记：
- This is needed to truncate an audit table
- An alternative would be to modify the insert query but this is probably less efficient
- SQL Server Express Edition does not have the SQL Server Agent
- 这是截断审计表所必需的
- 另一种方法是修改插入查询，但这可能效率较低
- SQL Server Express Edition 没有 SQL Server 代理
Related Questions:
相关问题：
回答by Raj More
Since SQL Server express does not come with SQL Agent, you can use the Windows scheduler to run a SQLCMD with a stored proc or a SQL script.
由于 SQL Server express 不附带 SQL Agent，因此您可以使用 Windows 调度程序来运行带有存储过程或 SQL 脚本的 SQLCMD。
回答by Thomas Bratt
I found the following mechanism worked for me.
我发现以下机制对我有用。
USE Master
GO
IF EXISTS( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[MyBackgroundTask]')
AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[MyBackgroundTask]
GO
CREATE PROCEDURE MyBackgroundTask
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- The interval between cleanup attempts
declare @timeToRun nvarchar(50)
set @timeToRun = '03:33:33'
while 1 = 1
begin
waitfor time @timeToRun
begin
execute [MyDatabaseName].[dbo].[MyDatabaseStoredProcedure];
end
end
END
GO
-- Run the procedure when the master database starts.
sp_procoption @ProcName = 'MyBackgroundTask',
@OptionName = 'startup',
@OptionValue = 'on'
GO
Some notes:
一些注意事项：
- It is worth writing an audit entry somewhere so that you can see that the query actually ran.
- The server needs rebooting once to ensure that the script runs the first time.
- 值得在某处编写审计条目，以便您可以看到查询实际运行。
- 服务器需要重新启动一次以确保脚本第一次运行。
回答by VoteCoffee
Create a scheduled task that calls "C:\YourDirNameHere\TaskScript.vbs" on startup. VBScript should perform repeated task execution (in this example, it's a 15 minute loop)
创建一个在启动时调用“C:\YourDirNameHere\TaskScript.vbs”的计划任务。VBScript 应该执行重复的任务执行（在这个例子中，它是一个 15 分钟的循环）
Via command line (must run cmd.exe as administrator):
通过命令行（必须以管理员身份运行 cmd.exe）：
schtasks.exe /create /tn "TaskNameHere" /tr "\"C:\YourDirNameHere\TaskScript.vbs\" " /sc ONSTARTUP
Example TaskScript.vbs: This executes your custom SQL script silently using RunSQLScript.bat
示例 TaskScript.vbs：这将使用 RunSQLScript.bat 静默执行您的自定义 SQL 脚本
Do While 1
WScript.Sleep(60000*15)
Set WshShell = CreateObject("WScript.Shell")
WshShell.RUN "cmd /c C:\YourDirNameHere\RunSQLScript.bat C:\YourDirNameHere\Some_TSQL_Script.sql", 0
Loop
RunSQLScript.bat: This uses sqlcmd to call the database instance and execute the SQL script
RunSQLScript.bat：这个使用sqlcmd调用数据库实例并执行SQL脚本
@echo off
sqlcmd -S .\SQLEXPRESS -i %1
回答by Yishai
If you are using Express Edition, you will need to use the Windows Scheduler or the application connecting to the server in some way.
如果您使用的是 Express Edition，则需要使用 Windows 调度程序或以某种方式连接到服务器的应用程序。
You would use the scheduler to run sqlcmd. Here are some instructionsfor getting the sqlcmd working with express edition.
回答by Iman
SQL Schedulerfrom http://www.lazycoding.com/products.aspx
来自http://www.lazycoding.com/products.aspx 的SQL 调度程序
- Free and simple
- Supports all versions of SQL Server 2000, 2005, and 2008
- Supports unlimited SQL Server instances with an unlimited number of jobs.
- Allows to easily schedule SQL Server maintenance tasks: backups, index rebuilds, integrity checks, etc.
- Runs as Windows Service
- Email notifications on job success and failure
- 自由而简单
- 支持所有版本的 SQL Server 2000、2005 和 2008
- 支持无限数量的作业的无限 SQL Server 实例。
- 允许轻松安排 SQL Server 维护任务：备份、索引重建、完整性检查等。
- 作为 Windows 服务运行
- 关于作业成功和失败的电子邮件通知
回答by Aaron Bertrand
Since another similar question was asked, and will likely be closed as a duplicate of this one, and there are many options not mentioned in the answers already present here...
由于提出了另一个类似的问题，并且可能会作为该问题的副本而关闭，并且这里已经存在的答案中没有提到许多选项......
Since you are using SQL Express you can't use SQL Server Agent. However there are many alternatives, all of which you can schedule using ATor Windows Task Schedulerdepending on your operating system:
由于您使用的是 SQL Express，因此您不能使用 SQL Server 代理。但是，有许多替代方案，您可以根据您的操作系统使用AT或Windows 任务计划程序来安排所有这些：
All of these languages/tools (and many others) have the capacity to connect to SQL Server and execute a stored procedure. You can also try these Agent replacements:
所有这些语言/工具（以及许多其他语言）都能够连接到 SQL Server 并执行存储过程。您还可以尝试这些代理替换：
回答by BrianMichaels
The easiest way I have found to tackle this issue is to create a query that executes the stored procedure then save it. The query should look similar to this one below.
我发现解决这个问题的最简单方法是创建一个执行存储过程的查询，然后保存它。查询应该类似于下面的这个。
use [database name]
exec storedproc.sql
Then create a batch file with something similar to the code below in it.
然后创建一个批处理文件，其中包含类似于以下代码的内容。
sqlcmd -S servername\SQLExpress -i c:\expressmaint.sql
Then have the task scheduler execute the batch as often as you like
然后让任务调度程序按您喜欢的频率执行批处理
回答by David Browne - Microsoft
Another approach to scheduling in SQL Express is to use Service Broker Conversation Timers. To run a stored procedure periodically, which you can use to bootstrap a custom scheduler.
在 SQL Express 中进行调度的另一种方法是使用Service Broker Conversation Timers。定期运行存储过程，您可以使用它来引导自定义调度程序。
回答by KennyKivi
Our company also use SQLEXPRESS and there is no SQL Agent.
我们公司也用SQLEXPRESS，没有SQL Agent。
Since there is no marked answer as "right" and all the solutions are quite complex I'll share what I did there. May be its really bad, but it worked great to me.
由于没有标记为“正确”的答案，并且所有解决方案都非常复杂，因此我将分享我在那里所做的事情。可能真的很糟糕，但对我来说效果很好。
I've chosen operations of Insertion (people do) to a table that got closely the same time range i needed and made a trigger "ON INSERT" that applies needed function.
我选择了对表的插入操作（人们这样做），该表的时间范围与我需要的时间范围非常接近，并制作了一个应用所需功能的触发器“ON INSERT”。
回答by Cade Roux
As you have correctly noted, without the agent process, you will need something else external to the server, perhaps a service you write and install or Windows scheduler.
正如您正确指出的那样，如果没有代理进程，您将需要服务器外部的其他东西，可能是您编写和安装的服务或 Windows 调度程序。
Note that with an Express installation for a local application, it is possible that the machine may not be on at the time you want to truncate the table (say you set it to truncate every night at midnight, but the user never has his machine on).
请注意，对于本地应用程序的 Express 安装，可能在您想要截断表时机器可能没有打开（假设您将其设置为每晚午夜截断，但用户从未打开他的机器）。
So your scheduled task is never run and your audit log gets out of control (this is a problem with SQL Server Agent as well, but one would assume that a real server would be running non-stop). A better strategy if this situation fits yours might be to have the application do it on demand when it detects that it has been more than X days since truncation or whatever your operation is.
因此，您的计划任务永远不会运行，并且您的审核日志会失控（这也是 SQL Server 代理的问题，但人们会假设真正的服务器将不间断地运行）。如果这种情况适合您，则更好的策略可能是让应用程序在检测到截断或您的操作已超过 X 天时按需执行此操作。
Another thing to look at is if you are talking about a Web Application, there might be time when the application is loaded, and the operation could be done when that event fires.
另一件要注意的事情是，如果您谈论的是 Web 应用程序，那么在加载应用程序时可能会有时间，并且可以在该事件触发时完成操作。
As mentioned in the comment, there is sp_procoption- this could allow your SP to run each time the engine is started - the drawbacks with this method are that for long-running instances, there might be a long time between calls, and it still has issues if the engine is not running at the times you need the operation to be done.
正如评论中提到的，有sp_procoption- 这可以让你的 SP 在每次引擎启动时运行 - 这种方法的缺点是对于长时间运行的实例，调用之间可能会有很长的时间，它仍然有如果发动机在您需要完成操作的时间没有运行，则会出现问题。
