問題描述
sql server 2005 數據庫郵件錯誤（操作已超時） (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
‑‑‑‑‑
參考解法
方法 1:
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.
方法 2:
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.
方法 3:
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'
(by Yasmine、Aaron Bertrand、Rod、Cocoa3338)