July 17, 2009 at 1:51 pm
Sometimes I need to automatically email query results. The process works fine for very small attachment, but if the query results in more than, I don't know, 400 rows, the email isn't sent. SSMS says: "Mail queued" but the email log says:
"Cannot send mails to mail server. (Exceeded storage allocation. The server response was: 4.3.1 Message size exceeds fixed maximum message size)".
I already increased the value in the Database Mail Configuration Wizard (Maximum File Size), but I still get the same error.
Does anybody know what I can try?
Thanks a lot,
Luiz.
July 17, 2009 at 2:31 pm
Looks like the error is being returned from the mail server because your mail server limits the size of mail messages. I don't think this is a SQL Server problem beyond the fact that you need to reduce the amount of data returned by the query.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 20, 2009 at 9:40 am
Email server seems to be ok. It accepts 10MB attachments. But I can't send a 50k file using SQL Server mail.
Any other suggestions?
Thanks.
July 20, 2009 at 9:53 am
Do you have anti-virus software running on the SQL Server? I know for a fact that McAfee blocks database mail unless you add an exception to the "Prevent Mass Mailing Worms from sending email" Access Protection rule.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 20, 2009 at 10:08 am
I do have anti-virus in the SQL Server.
SQL Server is able to send small emails but not big ones.
July 20, 2009 at 10:23 am
I've never seen that before, could it be an AV rule? What's the account you are sending too? Maybe their mailbox is full. Have you tried sending the email out to a gmail or hotmail account? Or you could try using gmail or hotmail as the smtp server to determine if it is the sending mail server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 20, 2009 at 10:35 am
I think Jack might have the key right there: Exceeded storage allocation. is mailbox full, right?
alternatively, maybe your server is set for 10 Megs, but the destination server's limit is smaller, maybe 1 or 2 meg? can you send the same email to an account with a known specific limit of 10 meg or more?
Lowell
July 20, 2009 at 11:03 am
I have tested sending to some of my personal email accounts (yahoo, hotmail, etc). None of them are full.
When I execute msdb.dbo.sp_send_dbmail proc, SSMS says "Mail queued" and SQl Mail log records:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2009-07-20T10:56:00). Exception Message: Cannot send mails to mail server. (Exceeded storage allocation. The server response was: 4.3.1 Message size exceeds fixed maximum message size).
This problem doesn't happen with small attachments.
July 20, 2009 at 11:16 am
You said your mail server is configured for sending up to 10Mb attachments. Maybe you need to send this email reports as an attachment (i.e with MIME encoding) as opposed to a plain text message. Just guessing here, but the error is coming from the SMTP server.
The probability of survival is inversely proportional to the angle of arrival.
July 20, 2009 at 11:25 am
I tried both: Sending the query result as attachment and sending as part of email body.
They both raise the error.
I'm begining to think this may be harder than I thought.:w00t:
July 20, 2009 at 11:50 am
Have you tried sending using google/yahoo/hotmail as the smtp server?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 21, 2009 at 9:06 am
Hi,
My .02 cents...if your running Exchange server, have your admin check the size (Exchange System Manager > Global Settings > Message Delivery Properties > Default tab)...
If that isn't the culprit, do you have your email server forwarding to an outgoing spam filter or perhaps an overly aggresive stateful firewall (e.g. Watchguard firewall)?
That might also kickback your outbound mail...
Jason.
July 22, 2009 at 8:19 am
hi, why dont you try to create a table where you insert the result as html or text and then you can send it?
i do this, and it works good and i dont have troubles with the size at least not for text.
EXEC msdb.dbo.sp_send_dbmail + values from your table as parameters.
ale
March 7, 2011 at 9:13 am
a bit late for this one but the following steps worked for me (this was configured on the server running SQL)...
Open the IIS Manager, expand your computer's name, scroll down to
"Default SMTP Server", right-click it and select "Properties" from the context menu.
Click the "Messages" tab and set a maximum message size, or allow any
size message by deselecting the "Limit maximum message size to" check-box.
May 6, 2011 at 11:50 am
Hello,
I was wondering if you every received a resolution to this problem, our site is experiencing the same issue. We have taken all the same steps as you have to resolve the problem with no success.
Thank you
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply