Only some e-mail gets sent successfully from sp_send_dbmail (Database Mail) on SQL Server 2005

Question

I want to find an answer to what is happening when I use sp_send_dbmail on SQL Server 2005. I have been googeling but without success and I don't know really where to turn now.

This is the scenario:

I have set up a mail function on our SQL server. I have the e-mail addresses in one table and the content in another table. For each address I find in my table, I use the sp_send_dbmail Stored Procedure to compile a new message to the address.

When I later execute my own SP that does the above (takes the email and compiles a new message to the recipient), DatabaseMail90.exe starts on the server and the e-mails are being sent out. But this is where it gets tricky, because some of the mail are being distributed directly, probably because it is on the same domain and network. The complicated thing is that most - not all - external mail gets an error in the msdb.dbo.sysmail_mailitems table. But the confusion does not stop here. Some of the e-mails are being delivered even though it has a reported error in the msdb.dbo.sysmail_mailitems table.

This is the query I use to view sent messages status:

SELECT
    mailitem_id,
    recipients,
    subject,
    send_request_date,
    sent_status, --0 new, not sent, 1 sent, 2 failure or 3 retry.
    sent_date
FROM 
    msdb.dbo.sysmail_mailitems
WHERE
    sent_status != 1

I have configured the Database Mail, System Parameters like this:

• Account Retry Attempts: 3
• Account Retry Delay (seconds): 30
• Maximum File Size (Bytes): 50000000 (NEVER going to be this big, but just to be on the safe side)
• Prohibited Attached File Extensions: exe, dll, vbs, js
• Database Mail Executable Minimum Lifetime (seconds): 300
• Logging Level: Extended

My Database Mail Profile, is Public and set as Default Profile. I also have set up an existing Profile.

This is the SP that executes everything and start sending the mail:

DECLARE @fldEmail AS VARCHAR(max)
DECLARE @fldSubject AS VARCHAR(max)
DECLARE @fldMessage AS VARCHAR(max)
DECLARE port_cursor CURSOR FOR
SELECT d.[E-Mail],s.Subject,s.HTMLMessage
FROM tbl_NAV_Sendout s,tbl_NAV_DistributionList d
OPEN port_cursor
FETCH NEXT FROM port_cursor
INTO @fldEmail, @fldSubject, @fldMessage
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC msdb.dbo.sp_send_dbmail @recipients = @fldEmail, @subject = @fldSubject, @body = @fldMessage, @body_format = 'HTML', @file_attachments = 'C:\NAVDW\ECNAV.csv', @profile_name = 'DBMail'
FETCH NEXT FROM port_cursor
INTO @fldEmail, @fldSubject, @fldMessage
END
CLOSE port_cursor
DEALLOCATE port_cursor

So the question remains. Is there a way for me to tweak the setup to deliver the e-mails faster? And also, the real question, why is there an error when sending the e-mail sometimes? There is nothing wrong with the e-mail address it is sending to. Where should I look to find possible errors in my code or my Database Mail configuration? Have you experienced something like this?

What is the description of an error in the msdb.dbo.sysmail_mailitems table? I mean, there is an error reported to an external domain, but the mail is delivered. Or if the mail is not delivered, where should I look on what is causing the problem.

Answer 1

Can you try adding WAITFOR DELAY function in your sql query as per below:

DECLARE @fldEmail AS VARCHAR(max)  
DECLARE @fldSubject AS VARCHAR(max)  
DECLARE @fldMessage AS VARCHAR(max)  
DECLARE port_cursor CURSOR FOR  
SELECT d.[E-Mail],s.Subject,s.HTMLMessage  
FROM tbl_NAV_Sendout s,tbl_NAV_DistributionList d  
OPEN port_cursor  
FETCH NEXT FROM port_cursor  
INTO @fldEmail, @fldSubject, @fldMessage  
WHILE @@FETCH_STATUS = 0  
BEGIN  
WAITFOR DELAY '000:00:10'  
EXEC msdb.dbo.sp_send_dbmail @recipients = @fldEmail, @subject = @fldSubject, @body =   @fldMessage, @body_format = 'HTML', @file_attachments = 'C:\NAVDW\ECNAV.csv',   @profile_name = 'DBMail'  
FETCH NEXT FROM port_cursor  
INTO @fldEmail, @fldSubject, @fldMessage  
END  
CLOSE port_cursor  
DEALLOCATE port_cursor  

Although this will slow the execution but adding this function will give the SQL Server sufficient time to efficiently populate the data using cursor and to deliver the email. It worked perfectly fine in my case.