Line feed every 1000 characters using email from SQL Stored Proc
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',@use
@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.
this is the line of code that calls the XPSMTP_Mail process
EXEC sp_send_XPSMTP_Mail 'admin@domain.com.au',@use
@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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You should also set the @type to "text/html" as the default is "text/plain".
ASKER
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>
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>
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>
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>
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>
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>
Have you changed the @type to "text/html"?
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!!
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.
>>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.
ASKER
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?
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?
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))
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))
>>The solution is not to take line breaks away but to add them<<
I would concur.
I would concur.
ASKER
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*
I had a subscript removing the linefeeds after I was adding them! *idiot*
ASKER