使用SQL Server作业自动发送电子邮件 [英] Sending emails automatically using SQL Server job

问题描述

我正在开发一个使用SQL Server作为数据库后端的.NET桌面应用程序。该应用程序的要求之一是，例如，如果某个记录状态在30天内保持不活动状态，则会向与该记录关联的用户发送提醒电子邮件。

I'm developing a .NET desktop application with SQL Server as the database backend. One of the requirements of the application is that if a record status, for example, remains inactive for 30 days, there will be a reminder email sent to the user associated to that record.

只要启动并运行，就可以在应用程序中轻松完成此操作。但是，假设在一段时间内，没有人启动应用程序，则不会发送提醒电子邮件，因为没有/点头触发了操作。

This could be done pretty easily within the application, as long as it is started and running. However, assume that for a certain period of time, nobody starts up the application, the reminder email won't be sent, because nothing / nodody triggers the action.

如何在SQL Server中创建可以监视记录并根据需要发送电子邮件的作业？

How about creating a job in SQL Server which can monitors the records and sends emails as needed? Has anyone ever done that?

非常感谢！

推荐答案

给定任务的要求，建议您创建一个控制台程序（带有C＃或VB.NET），以检查非活动（30天）行状况，然后生成适当的电子邮件通知消息。然后使用SQL Server代理作业每隔一个小时左右运行一次该程序（取决于检测不活动的行条件的紧迫性）。

Given the requirements of your task, I suggest that you create a console program (w/ C# or VB.NET) that checks for inactive (30 days) row condition and then generates the appropriate email notification message. Then run this program every hour or so (depending on the urgency involved in detecting an inactive row condition) using a SQL Server Agent Job.

下图显示了SQL如何服务器代理作业显示在SQL Server 2008 R2的对象资源管理器中。

The following image shows how the SQL Server Agent Jobs are displayed in the Object Explorer for SQL Server 2008 R2.

此 SO项涵盖了创建在特定时间运行的控制台程序的某些方面。 SQL Server Job Agent有几个计划选项可以满足您的需求。

This SO entry covers some aspects on creating a console program that runs at certain times. The SQL Server Job Agent has several scheduling options that should facilitate your needs.

您可能不愿意为此创建控制台程序，但是您容易发现这样做这样就为您提供了使用基于SQL Server的纯方法很难实现的选项。另外，您将来可能会需要这种方法提供的类似处理。

You might be reluctant to create a console program for this, but you are apt to find that doing so gives you options that are simply not easily implemented with a pure SQL Server based approach. Plus, you may have future needs that require similar processing that this approach provides.

这篇关于使用SQL Server作业自动发送电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！