sql server 2005 database mail error (The operation has timed out)
I have configured Database Mail for sending newsletter emails from sql database. I have a stored procedure that selects the emails of the subscribers and send them the mails like this:
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'ProfileName', @recipients = @email, @subject = @thesubject, @body = @themessage, @body_format = 'HTML' ;
The account used for sending is working fine coz when I sent the test mail after configuring the Database Mail it was sent successfully but the problem is sending more than one mail.
Currently there are 50 emails in the table after executing the stored procedure I checked the msdb tables sysmails_event_log, sysmails_faileditems and sysmails_sentitems, I found that only the 1st 12 emails were sent and the rest failed and the error message in the event_log was:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2009-12-01T16:55:11). Exception Message: Cannot send mails to mail server. (The operation has timed out.). )
I checked the emails no. 13 and laters and I didn't find anything wrong with them, so I tried executing the procedure again, this time it sent to the 1st 14 email and the rest failed with the same error so I changed the procedure and made it select the mails starting from mail no. 15 (as if it will send from record 15 to 50) and tested it, this time it sent to the 1st 13 email, I selected from 27 to 50 and executed, again it sent to the 1st 12.
I don't understand what's going on here what's this behavior of sending mails only in the range of 12-14 mails at a time.
Can anyone help me on this?
Thanks in advance
Where is the SMTP server located? Have you checked to see if you can fire more than 12 e-mails at a time through that SMTP server without SQL Server being involved at all? It looks like the problem is not SQL Server but some kind of policy on the SMTP server. There may be some kind of abuse prevention on the firewall or other network appliances in between the database server and the SMTP server as well. Have you considered setting up SMTP services on the database server, then setting up Database Mail so that it uses the local SMTP server instead of going over the network (and potentially running into the policy or policies alluded to above)? Of course you should set up SMTP securely, making sure that only connections from the local machine are allowed.
FIX: Error message when you send an e-mail by using Database , (The operation has timed out.). ) However, you cannot configure the time-out value for Database Mail in SQL Server 2005 before you apply this� However, you cannot configure the time-out value for Database Mail in SQL Server 2005 before you apply this cumulative update package. Resolution Cumulative update information
I solve this situation Doing 2 things:
- ReDoing the Database Mail Profile
- Restarting the Windows SMTP service
Hope it works for anyone who got the same situation.
Database Mail – SQLServerCentral, Database Mail – Learn more on the SQLServerCentral forums. (The operation has timed out.). After SP 1 2008 for sql server, I subsequently ran into problems and The error message in the log file MCTS : 2005, 2008 In Microsoft SQL Server 2005 or Microsoft SQL Server 2008, you may receive the following error message when you send an e-mail by using Database Mail: The mail could not be sent to the recipients because of the mail server failure. Sending Mail using <Account>
I met this problem also. My solution is to set delay for list of emails sending. And the problem was fixed.
WHILE @@FETCH_STATUS = 0 BEGIN WAITF OR DELAY '000:00:10'
How to configure database mail in SQL Server, Database Mail, as you would expect from its name, is a solution for This is going to happen from time to time because this is an The error description messages are not predefined in the SQL Server Engine. Everything looks good , but the e-mail is still not getting out. (The operation has timed out.). (The operation has timed out.). What's odd is that the e-mails do get sent out successfully the first time, then due to the default DB Mail Account configuration, the "retry" kicks in and a second e-mail gets sent out so the users are receiving duplicates.
Troubleshooting Database Mail Failures, Database Mail is a component of SQL Server that is available in If you have yet to define any, then check out a guide on configuring Database Mail in the links at the end The details of the failure, including time and error message. a TRUNCATE operation without backups � How to recover SQL Server� The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 7 (2013-04-30T10:23:57). Exception Message: Cannot send mails to mail server. (The operation has timed out.) I have wriitten on S P for snding mails which sends 94 mail to different ail address out of which only 35 are getting delivered.
Database Mail (sp_send_dbmail) fails when there are more than 20 , According to documentation "Database Mail is an enterprise solution for sending e-mail messages from the SQL Server Database Engine. Sending mails fails when there are more than 20 entries to be sent with the following error messages : Database Mail error server. (The operation has timed out.).". The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 5 (2017-04-14T21:34:10). Exception Message: Cannot send mails to mail server. (The operation has timed out.). ) I have tried several ways of setting up Database Mail, a mixture of precreated scripts and following the interface in SSMS.
Exception Message: Cannot send mails to mail server. (The operation has timed out.).” In my case we are attempting to send several hundred emails at a time from SQL job. Sometimes several hundred send successfully, other times ‘unsent’ emails stop sending, and instead go to status ‘retrying’ where they appear to stay for the designated timeout, at which point they fail with status ‘failed’. At this point the best we can do is stop and restart Database Mail using SP: USE msdb ; GO
- The SMTP server is located on the same domain of the database server and the SMTP server can fire more than 12 emails at a time with and without SQL Server being involved and I don't think there's any kind of abuse prevention between the database server and the SMTP server because before using Database Mail I was using XPSMTP extended procedure for sending mails from SQL and it was working fine and all emails were delivered, I only switched to Database Mail coz I needed to send a large message body as nvarchar(max) and the xpsmtp only allowed nvarchar(4000).
- So I thought the problem might be with the Database Mail and not the SMTP server since everything was fine before switching to Database Mail. And as for the solution of using the local SMTP Server I want this to be the last solution to go for. Thanks
- This is the extended procedure I used before Database Mail sqldev.net/xp/xpsmtp.htm
- I've used Gert's procedure with great success in the past as well, including on a 2005 server where we had a different problem with Database Mail (though I don't bother trying to send huge e-mails like you do). So, with the extended procedure you get more than 12 e-mails, but with Database Mail you don't? Have you tried a different SMTP server on the domain? What is wrong with putting an SMTP server on the local box?
- Am not sending huge emails it's a html newsletter to be sent to 50 to 100 people and it's size is greater than nvarchar(4000) it may reach 7000 or 8000 so when I was using the xpsmtp extended procedure the members received the newsletter but was truncated. And with the extended procedure the whole mail list was delieverd not only subsets of 12 emails at a time like it is with Database Mail.