• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 770
  • Last Modified:

sp_send_dbmail

Moving from XP_SMTP_SENDMAIL to SP_SEND_DBMAIL.

Not difficult, and test with one or 2 emails OK.

When I try to run several batches of email one works, the next one won't,

Same script, same account date selection is different.

Small batches OK

errors .....
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2012-09-13T13:46:30). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.1.1 User unknown). )
OR
"The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2012-09-21T10:56:43). Exception Message: Cannot send mails to mail server. (The operation has timed out.). )"
OR
"The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2012-09-21T10:56:49). Exception Message: Cannot send mails to mail server. (Failure sending mail.). )"

The IP for the server has no limits on sending mail at the barracuda for either the sending email address or the IP.

Frequently get more than 1 message in  one process?

The last process selected 86 records to process yet only 24 were actually sent?

What configuration are we missing?
0
Jeff_Kingston
Asked:
Jeff_Kingston
  • 2
  • 2
1 Solution
 
Jim P.Commented:
This is a one time run:

exec msdb.dbo.sysmail_configure_sp 'DatabaseMailExeMinimumLifeTime', '1200';
exec msdb.dbo.sysmail_configure_sp 'AccountRetryAttempts', 3;

Open in new window


That changes the configuration on the last part of the mail setup

This basically starts up the mail services to be ready to go.
IF NOT EXISTS (SELECT * FROM msdb.sys.service_queues
               WHERE name = N'ExternalMailQueue' AND is_receive_enabled = 1)
BEGIN
    EXEC @rc = msdb.dbo.sysmail_start_sp
END

Open in new window


And you still may get failures.
0
 
Jeff_KingstonAuthor Commented:
I will give this a try today, this job runs overnight.

One question...you say "And you still may get failures."  Is this due to more junk from Redmond or simply the nature of trying to get anything to truly run when the Microsoft brand is associated with it.  It seems every release has more errors, omissions, and total lack of any backwards compatibility, standard utilities are placed in a bingo ball and arbitrarily put all over the place at the whim of some engineer?  Upgrades with this company are simply a nightmare.
0
 
Jim P.Commented:
It's a matter of the the SQL Server agent sometimes getting overwhelmed doing other jobs while trying to mail agents. Your best bet is to do stuff at odd time -- start the job at 12:07. The next one at 12:14, instead of all at once.
0
 
Jeff_KingstonAuthor Commented:
I could buy that in a massive installation, but we have 40 employees and 30% of them spend their entire day working in Microsoft Office.
  I will accept your solution with the caveat that SQL Server is just not a good database solution, and as much as I dislike Larry Ellison, maybe Oracle should be the way to go.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now