Stored procedure using SP_SEND_DBMAIL sending dupl

Question

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 = '[e-mail]',
@copy_recipients = '[e-mail];[e-mail];[e-mail]',
@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.

Answer 1

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.

Answer 2

Make sure you are not having any other Update statements in any other triggers in side triggers on 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.

Answer 3

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.

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.

Answer 4

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.

Answer 5

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.

Answer 6

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>

There are a couple of solutions to this issue.

1. Break out each recipient as a separate email.
2. Remove the invalid recipient from the list
3. Set your retry setting in DBMail to 0