SQL 使用 SP_SEND_DBMAIL 向所有收件人发送重复电子邮件的存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15521646/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Stored procedure using SP_SEND_DBMAIL sending duplicate emails to all recipients
提问by andewM
I have a stored procedure that is run every night which is supposed to send the results of a query to several recipients. However on most days it ends up sending a duplicate email a minute later. The code I am using is as follows (all emails and database refs have been changed):
我有一个每晚运行的存储过程，它应该将查询结果发送给多个收件人。但是，在大多数情况下，它最终会在一分钟后发送重复的电子邮件。我使用的代码如下（所有电子邮件和数据库引用都已更改）：
EXEC msdb.dbo.sp_send_dbmail
@recipients = '[email protected]',
@copy_recipients = '[email protected];[email protected];[email protected]',
@subject = 'Example Email',
@profile_name = 'ExampleProfile',
@query = 'SELECT name
FROM table
WHERE date BETWEEN (getdate() - 1) AND getdate()',
@attach_query_result_as_file = 1
Any help with this would be greatly appreciated.
对此的任何帮助将不胜感激。
采纳答案by andewM
The solution has turned out to be reducing the number of Account Retry Account on the server to 0 (within the Database Mail Configuration Wizard).
解决方案是将服务器上的帐户重试帐户数减少到 0（在数据库邮件配置向导中）。
回答by Eric Higgins
This occurs because an address receiving the email (either in a group, or individual) has an email address that is no longer valid. While you can eliminate retries as in the accepted answer, the best approach is to clean up the distribution.
发生这种情况是因为接收电子邮件的地址（在组中或个人中）具有不再有效的电子邮件地址。虽然您可以像接受的答案一样消除重试，但最好的方法是清理分发。
回答by Hugh McDaid
I had a similar issue where we had multiple recipients in a single email, and it would generate 2 sent emails. The issue ended up being one of the recipients was no longer valid, and the retry would send the email to all recipients, not just the one that failed. There are a number of views in msdb that can help you find your invalid recipient. They start dbo.sysmail_<something>
我有一个类似的问题，我们在一封电子邮件中有多个收件人，它会生成 2 封已发送的电子邮件。问题最终是收件人之一不再有效，重试会将电子邮件发送给所有收件人，而不仅仅是失败的收件人。msdb 中有许多视图可以帮助您找到无效的收件人。他们启动 dbo.sysmail_<something>
There are a couple of solutions to this issue.
这个问题有几个解决方案。
- Break out each recipient as a separate email.
- Remove the invalid recipient from the list
- Set your retry setting in DBMail to 0
- 将每个收件人分解为单独的电子邮件。
- 从列表中删除无效收件人
- 将 DBMail 中的重试设置设置为 0
回答by Doreen
I had this same duplication issue while using a SELECT statement in @query to send constant text in the body of every email, in addition to using @body and @subject to send custom text depending on some condition.
除了根据某些条件使用@body 和@subject 发送自定义文本之外，我在@query 中使用SELECT 语句在每封电子邮件的正文中发送常量文本时也遇到了同样的重复问题。
One email contained both custom text and query text as expected. The duplicated email contained only the @query text (no custom text) with a system inserted subject line of "SQL Server Message".
一封电子邮件按预期包含自定义文本和查询文本。重复的电子邮件仅包含@query 文本（无自定义文本），系统插入主题行“SQL Server 消息”。
I ran SELECT * FROM msdb.dbo.sysmail_sentitems and sure enough email was being sent twice. A look at sysmail_configuration revealed that AccountRetryAttempts paramValue = 1.
我运行了 SELECT * FROM msdb.dbo.sysmail_sentitems 并且确定发送了两次电子邮件。查看 sysmail_configuration 发现 AccountRetryAttempts paramValue = 1。
The problem went away after I removed @query entirely from the stored procedure (executed the alteration), ran the sp. Then I put @query back in the sp, exec the alteration. After that, the emails began sending only once. Go figure.
在我从存储过程中完全删除 @query（执行更改）后，问题就消失了，运行了 sp。然后我将@query 放回 sp 中，执行更改。之后，电子邮件开始只发送一次。去搞清楚。
回答by Tinku Analytics
Make sure you are not having any other Update statements in any other triggers in side triggers on update.
确保在更新时的侧触发器中的任何其他触发器中没有任何其他 Update 语句。
Even i faced the similar problem, when i cross checked with my triggers I've seen that i used another Update statement in my another trigger. It caused multiple firing to the triggers. Hence two mails were triggered.
即使我遇到了类似的问题，当我与触发器交叉检查时，我发现我在另一个触发器中使用了另一个 Update 语句。它导致触发器多次触发。因此触发了两封邮件。
回答by Ken Clark
If it is sending duplicate emails to recipients then that means your SP is being called multiple times in a day. Check calling time set in your SQL job which is calling this SP. It should be once in a day to avoid duplicate emails.
如果它向收件人发送重复的电子邮件，则意味着您的 SP 在一天内被多次调用。检查调用此 SP 的 SQL 作业中设置的调用时间。应该每天一次，以避免重复的电子邮件。
回答by Nilesh Thakkar
If it is not being sent twice from SQL Server and not a problem of Mail Server as well, then make sure you're not checking mail in Outlook with filters for mail, then you may get the email twice.
如果它不是从 SQL Server 发送两次，而且不是邮件服务器的问题，那么请确保您没有使用邮件过滤器检查 Outlook 中的邮件，然后您可能会收到两次电子邮件。
回答by Igor Voplov
I'd suggest you add another table to your database that will hold the info on when was the last time an email sent to each recipient.
我建议您将另一个表添加到您的数据库中，该表将保存有关最后一次向每个收件人发送电子邮件的时间的信息。
Without such table you can't really know what's going on. What if you run the SP multiple times by accident? There is nothing that will prevent it from sending an email.
没有这样的表，你就无法真正知道发生了什么。如果您不小心多次运行 SP 怎么办？没有什么可以阻止它发送电子邮件。
Regarding this problem – does your mail server keep a copy in sent items? If it does you might want to check send date for all messages there. This might give you a good info on what's going on.
关于这个问题——您的邮件服务器是否在发送的邮件中保留了一份副本？如果是这样，您可能想检查那里所有消息的发送日期。这可能会为您提供有关正在发生的事情的好信息。
