11

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 = 'email1@email.com',
@copy_recipients = 'email2@email.com;email3@email.com;email4@email.com',
@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.

il_guru
  • 8,383
  • 2
  • 42
  • 51
andewM
  • 181
  • 1
  • 1
  • 7
  • The call seems good but how are you calling it? Are you sure you are not calling it two times? – il_guru Mar 20 '13 at 11:05
  • I am calling it via the Job Manager in SQL Server Agent and as far as I can see it is only being called once within the Job Scheduler. – andewM Mar 20 '13 at 11:15
  • Have you checked the log? – il_guru Mar 20 '13 at 11:16
  • I have just checked the log and the Job is only being run once a day and in each Job I can see that the Procedure is only being run once. – andewM Mar 20 '13 at 11:26
  • Run SELECT * FROM msdb.dbo.sysmail_sentitems is it showing two rows for the email in question? If not then this is a mail server issue. If it is then the stored procedure is being called twice. – twoleggedhorse Mar 20 '13 at 11:40
  • Also try SELECT * FROM dbo.sysmail_event_log for possible errors that have made SQL attempt sending twice. – twoleggedhorse Mar 20 '13 at 11:44
  • @twoleggedhorse I have run both of the queries you provided. I found that there was only one email sent and no errors occurred which were related to the mailitem_id. I will have to check the mail server and see if the duplicates are occurring there. If I find out why this is happening I will post the answer here. – andewM Mar 20 '13 at 12:23
  • Out of interest, are any of these recipients listed twice? Perhaps once as joe@bloggs.com and then again in a distribution list? I have heard of a bug in exchange that sends twice in this case. Do you have ActiveSync on an Exchange 2003 Server, that has a known bug if SP2 is not installed? – twoleggedhorse Mar 20 '13 at 13:33
  • We are not using any distribution lists and I have made sure that all of the email addresses are only listed once. After contacting the guys that run our mail server, I tried disabling the job to see if the SP was being called from somewhere else, however I found that this was not the case, so I ended up recreating the job to see if it fixed the problem. It seems to have done the trick. However I'll keep tabs on it for the rest of the week and make sure it only gets send once. – andewM Mar 27 '13 at 11:24

8 Answers8

7

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).

andewM
  • 181
  • 1
  • 1
  • 7
2

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.

Eric Higgins
  • 690
  • 5
  • 9
  • 1
    For people who ran into this, you may try to send a test email to all recipients then your SMTP relay should later on will notify you those addresses that it failed to deliver. – Circle Hsiao May 22 '23 at 08:01
1

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
Hugh McDaid
  • 11
  • 1
1

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.

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".

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.

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.

Doreen
  • 714
  • 2
  • 14
  • 36
  • [ref to who want to adjust retry count](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-configure-sp-transact-sql?view=sql-server-ver16) – Circle Hsiao May 22 '23 at 08:02
0

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.

Ken Clark
  • 2,500
  • 15
  • 15
  • Thanks for the answer, but I have checked the job list and I'm sure the procedure is only being run once. – andewM Mar 20 '13 at 12:23
0

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.

Nilesh Thakkar
  • 2,877
  • 1
  • 24
  • 43
0

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.

Igor Voplov
  • 973
  • 9
  • 7
  • Thank you for your input. I disabled the job to see if the procedure was being run elsewhere, however I found that this was not the case and ended up recreating the job to see if it fixed the problem. It seems to have done the trick. – andewM Mar 27 '13 at 11:25
  • I'm glad to know I helped :) – Igor Voplov Mar 28 '13 at 10:06
0

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.

Tinku Analytics
  • 1
  • 1