使用SQL Server作业自动发送电子邮件

Question

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

只要应用程序启动并运行，就可以轻松实现此操作。但是，假设在某段时间内没有人启动应用程序，则不会发送提醒电子邮件，因为没有任何东西/任何人触发该操作。

那么，在SQL Server中创建一个作业来监视记录并根据需要发送电子邮件如何？是否有人曾经这样做过？

非常感谢！

- tab87vn

---

我以前用过sp_send_dbmail。将其设置在存储过程中，然后定期安排运行以检查是否需要发送任何电子邮件。如果您想要实时电子邮件提醒，我想您也可以在触发器中使用它。 - 3N1GM4

你正在使用哪个版本的SQL Server？ - JohnH

@JohnH 我正在使用2008 @3N1GM4 我对SqlServer和t-sql都不是很了解，有关于sp_send_dbmail的教程吗？我会尝试自己找找看。 - tab87vn

Answer 1

根据您的任务要求，建议您创建一个控制台程序（使用C#或VB.NET），检查不活动的行条件（30天），然后生成适当的电子邮件通知消息。然后使用SQL Server Agent Job每小时运行此程序一次或更多（取决于检测不活动行条件的紧急程度）。

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

这个SO entry涵盖了创建在特定时间运行的控制台程序的一些方面。SQL Server作业代理有几个调度选项，应该能满足您的需求。

您可能不愿意为此创建一个控制台程序，但是您很可能会发现这样做可以给您提供一些纯基于SQL Server方法无法轻松实现的选项。此外，您可能有未来需要类似处理的需求，而这种方法提供了相应的解决方案。

- JohnH

---

作为一名C#开发人员，我发现这种方法比纯粹在服务器端实现（SqlServer和T-SQL并不是我的专长）更可行。因此，这种方法意味着我可以使用像MailMessage这样的类来连接SMTP服务，而不是使用T-SQL的sp_send_dbmail。仍然相同的是，该服务仍由SQL Server代理程序进行调度和触发。我的理解正确吗？ - tab87vn

正确，SQL Server代理提供了调度功能，以便您可以在定期时间间隔内运行控制台程序。这使您可以创建一个程序，在给定时间点通过数据进行一次通行。下一次运行将根据您在SQL Server代理中为程序创建的计划确定。我希望这个程序会在短暂的几秒钟内运行。然后，程序可以保持空闲状态，直到调度程序再次启动它进行下一次运行。 - JohnH

由于安全要求，我应该使用第一种方法，即使用服务器端存储过程发送电子邮件。我已经设置了数据库邮件配置文件，并成功地使用“sp_send_dbmail”发送了测试电子邮件。您有任何建议如何通过作业代理完成此操作吗？例如定期扫描数据库记录并找出过期记录以发送提醒电子邮件的功能。 - tab87vn

你所说的“第一种方法”，是指仅使用SQL Server而不创建控制台程序的方法吗？如果是这样，从我的角度来看，这会让你的任务更具挑战性。由于我没有使用过sp_send_dbmail，而你已经使用过，因此在这方面你比我有优势。如有需要，请发布另一个关于该主题的问题。我还建议您明确范围，例如电子邮件的格式如何，必须传达哪些信息，提醒邮件发送频率如何，每个用户是否发送多个邮件以及如何更新数据库以满足这些要求。祝你好运。 - JohnH

实际上我没有使用过 sp_send_dbmail，但创建一个定期发送电子邮件的作业很容易，我可以轻松自己解决。由于我不擅长 t-sql，现在对我来说主要挑战是创建一个或多个事务，以 (1) 从例如 user 表中选择符合某些条件的记录，(2) 将这些记录的电子邮件放入基于 sp_send_dbmail 的事务中，并且 (3) 更新这些记录的诸如 send_email 状态，从 false 更改为 true。内容并不重要，只是一些静态文本，我已经将时间设置为每小时。 - tab87vn

我在http://stackoverflow.com/questions/40886618/创建了一个后续问题，涉及自动发送电子邮件与MSSQLServer 2008。 - tab87vn