在SQL server上计划运行存储过程

Is it possible to set up somehow Microsoft SQL Server to run a stored procedure on regular basis?

是否有可能以某种方式设置Microsoft SQL Server定期运行存储过程?

8 个解决方案

#1

Yes, in MS SQL Server, you can create scheduled jobs. In SQL Management Studio, navigate to the server, then expand the SQL Server Agent item, and finally the Jobs folder to view, edit, add scheduled jobs.

是的，在MS SQL Server中，您可以创建调度的作业。在SQL Management Studio中，导航到服务器，然后展开SQL server代理项，最后是要查看、编辑和添加计划作业的Jobs文件夹。

#2

If MS SQL Server Express Edition is being used then SQL Server Agent is not available. I found the following worked for all editions:

如果正在使用MS SQL Server Express Edition，则SQL Server代理不可用。我发现以下所有版本都适用:

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.
服务器需要重新引导一次，以确保脚本第一次运行。
A related question is: How to run a stored procedure every day in SQL Server Express Edition?
一个相关的问题是:如何在SQL Server Express Edition中每天运行一个存储过程?

#3

Yes, if you use the SQL Server Agent.

是的，如果您使用SQL Server代理。

Open your Enterprise Manager, and go to the Management folder under the SQL Server instance you are interested in. There you will see the SQL Server Agent, and underneath that you will see a Jobs section.

打开企业管理器，进入感兴趣的SQL Server实例下的管理文件夹。在那里，您将看到SQL Server代理，在它的下面，您将看到Jobs部分。

Here you can create a new job and you will see a list of steps you will need to create. When you create a new step, you can specify the step to actually run a stored procedure (type TSQL Script). Choose the database, and then for the command section put in something like:

在这里，您可以创建一个新的作业，您将看到需要创建的步骤列表。创建新步骤时，可以指定实际运行存储过程的步骤(类型为TSQL脚本)。选择数据库，然后在命令部分输入如下内容:

exec MyStoredProcedure

That's the overview, post back here if you need any further advice.

这是概述，如果你需要任何进一步的建议，请在这里发回来。

[I actually thought I might get in first on this one, boy was I wrong :)]

(实际上我想我可能会在这个问题上领先，我错了:)

#4

Probably not the answer you are looking for, but I find it more useful to simply use Windows Server Task Scheduler

可能不是您正在寻找的答案，但我发现使用Windows Server任务调度器更有用

You can use directly the command sqlcmd.exe -S "." -d YourDataBase -Q "exec SP_YourJob"

您可以直接使用命令sqlcmd。exe - s”。“-d YourDataBase -Q "exec SP_YourJob"

Or even create a .bat file. So you can even 2x click on the task on demand.

甚至创建.bat文件。所以你甚至可以在任务需要时点击2x。

This has also been approached in this HERE

在这方面也有过类似的尝试

#5

I'll add one thing: where I'm at we used to have a bunch of batch jobs that ran every night. However, we're moving away from that to using a client application scheduled in windows scheduled tasks that kicks off each job. There are (at least) three reasons for this:

我要补充一点:我们过去有一堆每天晚上都要做的工作。然而，我们正在从这一点转移到使用在windows计划任务中调度的客户端应用程序来启动每个任务。(至少)有三个原因:

We have some console programs that need to run every night as well. This way all scheduled tasks can be in one place. Of course, this creates a single point of failure, but if the console jobs don't run we're gonna lose a day's work the next day anyway.
我们有一些控制台程序也需要每晚运行。这样，所有预定的任务都可以放在一个地方。当然，这会造成单点故障，但是如果控制台作业不运行，我们第二天就会失去一天的工作。
The program that kicks off the jobs captures print messages and errors from the server and writes them to a common application log for all our batch processes. It makes logging from withing the sql jobs much simpler.
启动作业的程序从服务器捕获打印消息和错误，并将它们写入所有批处理进程的公共应用程序日志中。它使从withing sql作业中进行日志记录变得更加简单。
If we ever need to upgrade the server (and we are hoping to do this soon) we don't need to worry about moving the jobs over. Just re-point the application once.
如果我们需要升级服务器(我们希望尽快升级)，我们不需要担心将作业移到别处。只需重新指向应用程序一次。

It's a real short VB.Net app: I can post code if any one is interested.

它是一个很短的VB。Net app:如果有人感兴趣，我可以发布代码。

#6

Using Management Studio - you may create a Job (unter SQL Server Agent) One Job may include several Steps from T-SQL scripts up to SSIS Packages

使用Management Studio——您可以创建一个作业(unter SQL Server Agent)，一个作业可以包含从T-SQL脚本到SSIS包的多个步骤

Jeb was faster ;)

杰布更快,)

#7

You should look at a job scheduled using the SQL Server Agent.

您应该查看使用SQL Server代理调度的作业。

#8

You could use SQL Server Service Broker to create custom made mechanism.

您可以使用SQL Server服务代理创建自定义的机制。

Idea (simplified):

想法(简体):

Write a stored procedure/trigger that begins a conversation (BEGIN DIALOG) as loopback (FROM my_service TO my_service) - get conversation handler

编写一个开始对话(开始对话)的存储过程/触发器作为环回(从my_service到my_service)—获取会话处理程序
```
DECLARE @dialog UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @dialog
        FROM SERVICE   [name] 
        TO SERVICE      'name' 
        ...;
```

Start the conversation timer

开始对话计时器

DECLARE @time INT;
BEGIN CONVERSATION TIMER (@dialog)  TIMEOUT = @time;

After specified number of seconds a message will be sent to a service. It will be enqueued with associated queue.

在指定的秒数之后，消息将被发送到服务。它将与相关队列一起排队。

CREATE QUEUE queue_name WITH STATUS = ON, RETENTION = OFF
             , ACTIVATION (STATUS = ON, PROCEDURE_NAME = <procedure_name>
             , MAX_QUEUE_READERS = 20, EXECUTE AS N'dbo')
              , POISON_MESSAGE_HANDLING (STATUS = ON)

Procedure will execute specific code and reanable timer to fire again.

过程将执行特定的代码和可重新启动的计时器再次启动。

You can find full-baked solution(T-SQL) written by Michał Gołoś called Task Scheduler

你可以找到full-baked解决方案(t - sql)写的MichałGołoś称为任务调度器

Key points from blog:

重点从博客:

Pros:

优点:

Supported on each version (from Express to Enterprise). SQL Server Agent Job is not available for SQL Server Express
支持每个版本(从Express到Enterprise)。对于SQL Server Express, SQL Server代理作业是不可用的。
Scoped to database level. You could easiliy move database with associated tasks (especially when you have to move around 100 jobs from one enviromnent to another)
数据库级别的范围。您可以轻松地将数据库与相关任务移动(特别是当您必须将大约100个作业从一个环境移动到另一个环境时)
Lower privileges needed to see/manipulate tasks(database level)
查看/操作任务所需的较低权限(数据库级)

Proposed distinction:

提出的区别:

SQL Server Agent (maintenance):

SQL Server代理(维护):

backups
备份
index/statistics rebuilds
索引/统计重建
replication
复制

Task Scheduler (business processes):

任务调度器(业务流程):

removing old data
删除旧数据
preaggregations/cyclic recalculations
preaggregations /循环重新计算
denormalization
反规范化

How to set it up:

如何设置:

get source code from section: "Do pobrania" - To download (enabling broker/setting up schema tsks/configuration table + triggers + stored procedure)/setting up broker things)
从“Do pobrania”小节获取源代码——下载(启用代理/设置模式tsks/配置表+触发器+存储过程)
set up configuration table [tsks].[tsksx_task_scheduler] to add new tasks (columns names are self-descriptive, sample task included)
设置配置表[tsks]。[tsksx_task_scheduler]添加新任务(列名称是自描述性的，包括示例任务)

Warning: Blog is written in Polish but associated source code is in English and it is easy to follow.

警告:Blog是用波兰语编写的，但是相关的源代码是用英语编写的，很容易理解。

Warning 2: Before you use it, please make sure you have tested it on non-production environment.

警告2:在使用之前，请确保您在非生产环境中测试过它。

#1

#2