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

  • Question

  • Hi, this is my first attempt on this forum 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 (SP) 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.

    Thanks in advance.

    /Henrik

    • Edited by Henrik Jutterström Monday, February 28, 2011 8:17 AM Changed status codes comments
    Friday, February 25, 2011 1:31 PM

Answers

  • Ok. I finally found out the answer to this wierd problem.

    It all goes down to the mailserver. After a while I was able to use an other mailserver. A pure and simple SMTP-server with no checks or anything. Everything went really(!) fast this time and with no errors.

    By looking at the mailheaders I figured out that the mails where also beeing archeved at another service provider and without doing this and just sending the mail as fast as I could through the SMTP-server it all worked really great! Previously we used Microsoft Exchange 2010 (no SP installed) and it seems that this was the problem.

    I also found out that the internal mail was send immediately and that there was anouther queue for external mails.

    I also found out that the service DatabaseMail90.exe sometimes stoped and thus relating to that external mail where not sent (since internal mail was sent right away). I used the following commands to start and stop the service. And when I started it again, it picked up right where it left of, if there were any mails left to send out.

    exec msdb.dbo.sysmail_start_sp
    exec msdb.dbo.sysmail_stop_sp

    Thanks for all help and good luck in the future!

    /Henrik

    Friday, April 8, 2011 8:33 AM

All replies

  • it sounds like setup with your SMTP mail servers. what's your domain smtp server setup? does it support rely mail from your host?

    If you think my suggestion is useful, please rate it as helpful.
    If it has helped you to resolve the problem, please Mark it as Answer.

    Sevengiants.com
    Friday, February 25, 2011 2:59 PM
  • Thank you SevenKnights for your answer. I do agree with you that the issue is instread on the SMTP mail server.

    My current setup uses a relay address som I guess it supports relay. And pretty much every mail is sent successfully, alltough they are marked as "failures" in the msdb.dbo.sysmail_mailitems table.

    Do you know what categorizes a failure in the msdb.dbo.sysmail_mailitems table?

    Can you be a little more specific on the question you asked me regarding the domain smtp server setup. What should I look/ask for?

    /Henrik

    Friday, February 25, 2011 3:07 PM
  • I think this link will be helpful for you

    http://social.msdn.microsoft.com/Forums/en/sqldatawarehousing/thread/2f1e4e5d-f41f-4751-a4ee-1b71f493466c

     

    You can get @@errorcode somewhere to see exact error for failed emails.


    If you think my suggestion is useful, please rate it as helpful.
    If it has helped you to resolve the problem, please Mark it as Answer.

    Sevengiants.com
    Friday, February 25, 2011 3:10 PM
  • My reply to that answer is:

    The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2011-02-24T17:41:31). Exception Message: Cannot send mails to mail server. (Service not available, closing transmission channel. The server response was: 4.3.2 The maximum number of concurrent connections has exceeded a limit, closing transmission channel). )

    I should probobly ask the mail server people about this...

    /Henrik

    Friday, February 25, 2011 3:29 PM
  • Probably you are sending too quickly.

     

    Try put a delay in between two sends. WAITFOR


    If you think my suggestion is useful, please rate it as helpful.
    If it has helped you to resolve the problem, please Mark it as Answer.

    Sevengiants.com
    Friday, February 25, 2011 3:33 PM
  • HI can you send just one email to all recipients instead of one email per recipient?  If so then you can use a query like this.

    select distinct subject
    	, msg
    	, recipients
    from temail
    cross apply (
    	select recipient + ';' 
    	from temail
    	for xml path('')
    ) recipients(recipients)
    

    Also this is the return codes

     

    declare @returncode int

    EXEC @returncode = msdb.dbo.sp_send_dbmail
      @profile_name = 'automail',
      @recipients = '',
      @body = 'The stored procedure finished successfully.',
      @subject = 'Automated Success Message'

    select @@error, @returncode

    Return Value Error Number Description
    ============================================
    0 NA Success Code

    1 Will provide the below  Failure Code
    error number

    2 14618 Attach results must be specified

    3 14618 No output must be specified

    4 14618 No header must be specified

    5 14618 Check if query_result_separator is specifed

    6 14618 Echo error must be specified

    13 14626 @body_format can be TEXT (default) or HTML

    15 14618 Importance must be specified

    16 14622 Importance must be one of the predefined values

    17 14618 Sensitivity must be specified

    18 14623 Sensitivity must be one of predefined values

    19 14624 Message body cannot be null. Atleast one of message, subject, query,attachments must  be specified.

    20 14624 Recipients cannot be empty. Atleast one of the To, Cc, Bcc must be specified

    21 14625 If query is not specified, attach results and no header cannot be true.

     

    Friday, February 25, 2011 3:36 PM
  • Hi, and thank you for your effort in solving my question.

    Unfortunetly this sendout function damands me to send one mail per recipient.

    /Henrik

    Monday, February 28, 2011 8:20 AM
  • The thing is that I would like to send them out as quick as possible, so the delay is probobly not the perfect solution. I will investigage if we can tweak the settings for the mailserver tough. I really don't think there's a problem with the code. It's more of a configuration thing with the server.

    /Henrik

    Monday, February 28, 2011 8:26 AM
  • Yes, of course. You can set the SMTP server, you just need ask them to increase the number of concurrent connections.

    However, if you have thousands of recipients, you may want to do them in batches, since too many concurrent connections may overload your SMTP server.


    If you think my suggestion is useful, please rate it as helpful.
    If it has helped you to resolve the problem, please Mark it as Answer.

    Sevengiants.com
    Monday, February 28, 2011 9:29 AM
  • Ok. I finally found out the answer to this wierd problem.

    It all goes down to the mailserver. After a while I was able to use an other mailserver. A pure and simple SMTP-server with no checks or anything. Everything went really(!) fast this time and with no errors.

    By looking at the mailheaders I figured out that the mails where also beeing archeved at another service provider and without doing this and just sending the mail as fast as I could through the SMTP-server it all worked really great! Previously we used Microsoft Exchange 2010 (no SP installed) and it seems that this was the problem.

    I also found out that the internal mail was send immediately and that there was anouther queue for external mails.

    I also found out that the service DatabaseMail90.exe sometimes stoped and thus relating to that external mail where not sent (since internal mail was sent right away). I used the following commands to start and stop the service. And when I started it again, it picked up right where it left of, if there were any mails left to send out.

    exec msdb.dbo.sysmail_start_sp
    exec msdb.dbo.sysmail_stop_sp

    Thanks for all help and good luck in the future!

    /Henrik

    Friday, April 8, 2011 8:33 AM