I have several (12) stored procedures that are programmed as steps in a job that runs every night. Each stored procedure sends the results of a query to a few recipients. There are actually 4 procedures- each one associated with one of three databases and a different set of recipients to make 12 total. One of the recipient's email address became invalid. When that occurred, one (but only one!) of the four procedures began generating duplicate emails.
I understand from Stored procedure using SP_SEND_DBMAIL sending duplicate emails to all recipients that an invalid email address can initiate retry attempts. So, I've removed the offending address and also reduced the number of Account Retry to 0 on the DBMail Config. But the emails are still generated. They are sent to the invalid email address that has been removed as well as the other recipients.
If I run the job manually, the duplicates are not generated. I have tried deleting and re-creating the job, but the issue persists. I have changed the schedule of the job to send at another time- the duplicate emails are sent at the same time as the original schedule even though it has been deleted. Looking at the job/mail history, the offending emails do NOT appear.
Is this some sort of bug? The SQL of the procedure is shown below for reference:
DECLARE @qry varchar(2000)
SET @qry = 'SELECT Fields FROM TABLE WHERE Conditions'
SET NOCOUNT ON
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Example Email',
@from_address = '[email protected]',
@recipients = '[email protected]; [email protected]',
@subject = 'SubjectLine',
@body = 'BodyText',
@query = @qry;