?
Solved

Line feed every 1000 characters using email from SQL Stored Proc

Posted on 2006-04-30
11
Medium Priority
?
744 Views
Last Modified: 2013-12-03
Hello folks, really hope for a quick turnaround on this one and any silly suggestions greatly appreciated.

this is the line of code that calls the XPSMTP_Mail process

EXEC sp_send_XPSMTP_Mail 'admin@domain.com.au',@username,'domain','support@domain.com.au',@SubjectLine,'','support@domain.com.au', @HTML


@HTML is ...  DECLARE @HTML varchar(8000)
 I have removed every linefeed character I can think of from the @HTML variable

SET @HTML = REPLACE(@HTML, char(13), '')
SET @HTML = REPLACE(@HTML, char(12), '')
SET @HTML = REPLACE(@HTML, char(10), '')
SET @HTML = REPLACE(@HTML, char(9), '')

SET @HTML = REPLACE(@HTML, char(8), '')
SET @HTML = REPLACE(@HTML, char(27), '')

SET @HTML = REPLACE(@HTML, char(11), '')
SET @HTML = REPLACE(@HTML, char(14), '')

I'm at a loss. The variable is about 4000 chars long and there is damn space slotting in every 1000 characters (which messes with links and images etc) when I read the final output in MS Outlook.
0
Comment
Question by:Clever_Bob
11 Comments
 
LVL 7

Author Comment

by:Clever_Bob
ID: 16572527
Also, when I do a debug print in SQL Query, there are no spaces in the variable!
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 16572728
This is all covered in the FAQ.  See here:
http://www.sqldev.net/xp/xpsmtp.htm#FAQ

P.S. It is a carriage return/line feed (not just a line feed)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16572732
You should also set the @type to "text/html" as the default is "text/plain".
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Author Comment

by:Clever_Bob
ID: 16574037
Excellent - that helps to understand the problem thanks acperkins. Unfortunately I still haven't devised a solution.

I note that the 'maximum line length' is 1000 characters which is obviously the problem (although I don't understand why it manifests as a 'space' rather than a linefeed in my email client.

I have added a whole bunch of linefeeds into my message variable like this

SET @HTML = REPLACE(@HTML,'<linebreak></linebreak>', char(13) + char(10))

This creates a line break where I want it but then it also sets the line break every 1000 characters as well !!!

I tried this SET @HTML = REPLACE(@HTML,'<linebreak></linebreak>', char(13) + '|' + char(10))

as suggested here http://www.sqldev.net/xp/xpsmtp.htm#FAQ but the '|' comes out in my email text ?!

and this doesn't work either (just comes out in the text) SET @HTML = REPLACE(@HTML,'<linebreak></linebreak>', '(\r\n)')

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16574836
Have you changed the @type to "text/html"?
0
 
LVL 44

Expert Comment

by:scrathcyboy
ID: 16575054
Apparently you not understand HTML.  WHo said an HTML line can run on forever?  That is NOT in the specs.  The number of HTML characters are limited by (1) the width of the data structure (like table) you specify, and the FONT of the characters that fit in that data structure.  If you realized this, you would not be expecting endless line lengths from HTML, which is SCREEN ORIENTED, NOT printing oriented.  So instead of trying to have endless line length and let the printer wrap lines, this is totally WRONG for HTML.  ALL HTML set line/character width by table width, or else CSS display width.  Read up on HTML and you see where you seriously err.  So solution is to decide on fixed width for the FONT SIZE you want to print in -- everyone do this -- then you make code to wrap at that width, or just let HTML do it for you.  All you have to do is let the USER print the HTML file as he sees it on screen, it is SO SO simple!!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16575075
scrathcyboy,

>>Apparently you not understand HTML. <<
To put it mildly, chill out.  You make a valid point in that there should be no harm in having CrLf's embedded in HTML code, however your tone is to say the least, not coducive to a civil discussion.  This is not any of the web Topic Areas, so you should be prepared to politely answer questions that may not be so advanced in that area.  Otherwise, don't bother.

I trust you understand and I thank you for pointing that out.
0
 
LVL 7

Author Comment

by:Clever_Bob
ID: 16575183
Hi acperkins: Yes the @type was already set to "text/html"

scrathcyboy: I'm sincerely grateful for your help.

"Apparently you not understand HTML": Well jeez, somebody should tell the guys that are paying me $1000/day to write ASP/HTML for them!

"Who said an HTML line can run on forever?": Not me, was it you?

I'm afraid I don't understand the rest of your response at all. The only limitation of the XPSMTP_Mail process is that the max line length is 1000 chars. I understand this perfectly but inserting the linebreaks isn't helping me. I can't see how Font size is an issue here at all, we are only worried about the 1000 characters. I'm not sure how you got the idea that I want to PRINT anything. This is not a wrapping issue or even an HTML issue, its a XPSMTP_Mail process issue.

My HTML looks fine once it has been sent to an email client except for the spaces every 1000 characters (actually 998 characters).

Any more ideas anybody?




0
 
LVL 5

Expert Comment

by:lostcarpark
ID: 16576171
I think the problem is that XPSMTP is enforcing a maximum line length of 1000 characters (including the two CR+LF chars), so when you send it a string up to 8000 chars with no line breaks it inserts them when it thinks it needs to.

The solution is not to take line breaks away but to add them, thus preventing them from ever reaching 998 characters long. If you wanted to be smart, you could write a text scanner to find the 998th character then backtrack to the nearest suitable place to insert a line break. SQL is not the easiest language for text parsing, however.

An easier way would be to just insert a line break at suitable points in the HTML.

For example:

SET @HTML = REPLACE(@HTML, '</p>', char(13) + char(10))
SET @HTML = REPLACE(@HTML, '<br />', char(13) + char(10))
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16582375
>>The solution is not to take line breaks away but to add them<<
I would concur.
0
 
LVL 7

Author Comment

by:Clever_Bob
ID: 16583519
Hello folks - simple error in the end - all the most annoying programming problems are I reckon.
I had a subscript removing the linefeeds after I was adding them! *idiot*

0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question