如何在SQL Server Express Edition中每天运行存储过程？

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代理

10 个解决方案

#1

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代理，因此您可以使用Windows调度程序运行带有存储过程或SQL脚本的SQLCMD。

http://msdn.microsoft.com/en-us/library/ms162773.aspx

#2

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.
服务器需要重新启动一次以确保脚本第一次运行。

#3

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 Scheduler或连接到服务器的应用程序。

You would use the scheduler to run sqlcmd. Here are some instructions for getting the sqlcmd working with express edition.

您可以使用调度程序来运行sqlcmd。以下是使sqlcmd使用快速版的一些说明。

#4

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

#5

SQL Scheduler from http://www.lazycoding.com/products.aspx

来自http://www.lazycoding.com/products.aspx的SQL Scheduler

Free and simple
*而简单
Supports all versions of SQL Server 2000, 2005, and 2008
支持SQL Server 2000,2005和2008的所有版本
Supports unlimited SQL Server instances with an unlimited number of jobs.
支持无限数量的作业的无限SQL Server实例。
Allows to easily schedule SQL Server maintenance tasks: backups, index rebuilds, integrity checks, etc.
允许轻松安排SQL Server维护任务：备份，索引重建，完整性检查等。
Runs as Windows Service
作为Windows服务运行
Email notifications on job success and failure
有关工作成功和失败的电子邮件通知

#6

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 AT or Windows Task Scheduler depending on your operating system:

由于您使用的是SQL Express，因此无法使用SQL Server代理。但是，有许多替代方案，您可以使用AT或Windows任务计划程序安排所有这些替代方案，具体取决于您的操作系统：

VBScript
VBScript中
C# command line app
C＃命令行应用程序
batch file with SQLCMD
使用SQLCMD的批处理文件
PowerShell
电源外壳

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并执行存储过程。您还可以尝试这些代理替换：

SQLScheduler
SQLScheduler
Express Agent
快递代理
Standalone SQL Agent (beta)
独立SQL代理（测试版）

#7

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

然后让任务调度程序根据您的需要随时执行批处理

#8

You could use Task Scheduler to fire a simple console app that would execute the Sql statement.

您可以使用任务计划程序来触发将执行Sql语句的简单控制台应用程序。

#9

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天或者您的操作时间超过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在每次引擎启动时运行 - 这种方法的缺点是对于长时间运行的实例，调用之间可能会有很长时间，并且它仍然有如果引擎在您需要完成操作的时间没有运行，则会出现问题。

#10

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.

我选择了Insertion（人们做）的操作到一个表格，该表格与我需要的时间范围非常接近，并且触发“ON INSERT”应用所需的功能。

#1