存储过程中发送邮件的状态 [英] Status of Send mail in stored proc

问题描述

大家好，

我正在编写一个存储过程，会向用户发送电子邮件。我将使用sp_send_dbmail proc。

I am writing a stored proc that will send an email to users. I will be using the sp_send_dbmail proc for this.

但是，我希望存储过程根据发送的状态电子邮件更新表。

However, i want the stored proc to update a table based on the status email sent.

因此，如果电子邮件发送成功，它会将表格中的状态列设置为"电子邮件已成功发送"

So if the email was sent successful, it will set the status column in the table to 'Email Sent Successful'

如果电子邮件未成功，该字段将设置为"电子邮件失败" '

if the email was not successful, the field will be set to 'Email Failed'

关于如何在一个存储过程中完成所有这些任务的任何想法。

Any idea on how to have all these task in one stored proc.

me

推荐答案

您好lilutchay，

Hi lilutchay,

1. 您可以使用TRY / CATCH构造来记录您需要的内容 
   sp_send_dbmail proc。调用。
2. 此外，这里有一些有用的查询来检查SQL Server电子邮件子系统：

1. You can user TRY/CATCH construct to log what you need for the sp_send_dbmail proc. calls.
2. Additionally, here is some useful queries to check SQL Server email sub-system:

use msdb;
GO

-- Contains the bits necessary to interact with SMTP server
select * from sysmail_account;
-- Contains useful logs to inspect if something goes wrong
select * from sysmail_log;
-- Contains details of each email sent
select * from sysmail_mailitems;
-- Contains flags, bits, and values that drive email behavior
select * from sysmail_configuration;
-- Provides the identifier the sp_send_dbmail proc uses to trigger an email
select * from sysmail_profile;
-- Associative table between sysmail_profile and sysmail_account
select * from sysmail_profileaccount;
-- Contains configuration around the SMTP server used to send mail
select * from sysmail_server;

有用的链接：

检查使用数据库邮件发送的电子邮件的状态

这篇关于存储过程中发送邮件的状态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！