How eliminate extra space being addded to SQL mails?
-
Problem: SQL mail being sent successfully to external users via the Internet is too large, with additional space being added to every line of the body text. Recipients complain about “too large e-mails” (up to 500KB).
Problem environment: SQL mail on SQL 2000 SP3a installed on Win Srvr 2003 Std. Ed. with Outlook 2000 e-mail client. Sending to Exchange 2003 in POP3 configuration on Win Srvr 2003 Std Ed. Both servers on Active Directory. On Exchange server have: Global Settings> Internet Message Formats> Default> Properties> Message Format> set to MIME and Provide message body as plain text, and Servers> “named server”> Protocols> POP3> Default POP3 Virtual Server> Properties> Message Format> also set to MIME and Provide message body as plain text.
Problem doesn’t occur in test environment using same SQL code. Test environment is: SQL mail on SQL 2000 SP3a on NT4.0 Srvr SP6a with Outlook 2000 client, sending to Exchange 5.5 in POP3 config on NT4.0 Srvr SP6a. Exchange 5.5 is configured to send plain text. Both test servers on an NT domain. Test environment generated e-mails of 50KB or less with same text, without space being added to e-mail body. Anyone else had this problem? Any suggestions?
-
Haven't seen this. Are the mails being sent with just he extended stored procedures? If you send a message from outlook with the same profile does it still happen?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Steve,
Thank you for your excellent suggestion about sending from the e-mail client on the SQL server to either rule out or identify the e-mail client and Exchange settings as possible causes. I'd ignored Sherlock Holmes key principle: "Eliminate all other factors, and the one which remains must be the truth." (from The Sign of (the) Four)
I'll keep everone posted on the final outcome.
-
Problem solved on production servers. We were able to reduce the size of the empty space being added to our automated e-mail reports (generated by using SQL mail) to 17% of their original size by reducing the field in the holding table from 2000 char down to 150 char. The received e-mails went from 142K to 25K in steadily decreasing increments as we reduced that field size. However, this is an apparent anomaly from what our SQL developers thought they knew about SQL mail and the way it’s supposed to handle queries. We had also run another report holding significantly more data in our test server environment (described earlier) without any reduction in holding table size and the received e-mail was only 11K in size. Moreover, in our test server environment, when we reduced the field size from 2000 to 125, there was no reduction in size of the e-mails recipients actually received.
Since the SQL versions and Service Packs are identical in both production and test environments, and the problem doesn't occur when sending an identical test message directly from the Outlook 2000 client on the SQL server when logged in with the SQL agent profile, this would suggest that the difference in e-mail sizes is either due to the different OS that SQL 2000 is running on (Windows 2003 in production versus Windows NT 4.0 SP6a in test) or some other undiscovered variable. This would be the first difference our developers have reported encountering in the way the same version of SQL Server works based on a different OS.
Although the production problem's fixed, if anyone else has some thoughts on why there's a difference in these two environments, I'd like to learn more.
-
if you use field type 'char', it will treat any empty characters as a space, and thus having all the spaces in your email. Change the field type to 'varchar' and it should reduce your email size (no more spaces) - although you might have to update the field using rtrim function first after converting to varchar to remove all the trailing spaces.
another solution is to use the rtrim function for the email body.
PS: I know this is an old thread, but I was looking for other discussions about email and found this
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply