Create Line Break

Question

Create Line Break

JimS-Indy

SSCarpal Tunnel

Points: 4514
More actions
July 1, 2012 at 2:26 am

#271924

I'm using SS2005 on my client's machine. My ACCESS 2007 app creates table rows in my tblEventLog table. I want to extract certain of those event logs and send them to my email account periodically. I thought I'd use this as an opportunity to learn some new stuff.
I also have a table (tblPendingMessages) where I place messages that are periodically dispatched using a SQL Server Scheduled Job.
I've written the following query:
BEGIN TRAN
declare @CurrentDate datetime,
@Body varchar(max),
@TableHead varchar(max),
@TableTail varchar(max)
Set nocount on;
SELECT @CurrentDate=DATEADD(dw, -3, getdate())--Three weekdays back
--SET @CurrentDate=getdate()
SET @TableTail='</table></body></html>'
Set @TableHead = '<html><head>' +
'<style>' +
'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:9pt;} ' +
'</style>' +
'</head>' +
'<body><table cellpadding=0 cellspacing=0 border=0>' +
'<tr bgcolor=#FFEFD8><td align=center><b>User Name</b></td>' +
'<td align=center><b>Date</b></td>' +
'<td align=center><b>Message</b></td></tr>';
SELECT @Body=(
select ELogUserName as
,
CONVERT(nvarchar(20),Elogdatetime,101) as
,
Replace(Elogmessage,char(13),'') as
from tbleventlog
where elogdatetime > @CurrentDate AND elogeventtype = 3 AND elogusername <> 'jamesshaffer'
for xml raw('tr'), Elements
)
-- Replace the entity codes and row numbers
Set @Body = Replace(@Body, '_x0020_', space(1))
Set @Body = Replace(@Body, '_x003D_', '=')
Select @Body = @TableHead + @Body + @TableTail
-- return output
INSERT INTO tblPendingMessages
SELECT
'xxxxx@Gmail.com' as Recipient,
'CPAS Error Log' as MessageSubject,
null as MessageCC,
@Body as MessageBody,
@CurrentDate as MessageGenerated,
Null as MessageSent,
Null as MessageUser,
'HTML' as MessageFormat
--Select @Body
COMMIT
--ROLLBACK
Now, the Replace() function that extract the message line tries to eliminate CRLF characters inserted by ACCESS (on purpose, though that purpose may now be moot....) I tried using this code, but the email simply displayed the code and did not break. Any idea how to do it?
Replace(Elogmessage,char(13),'<br />') as
Jim

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Rick Krueger SSC Eights! Points: 856 More actions · Answer 1

Right now, you are only replacing the Cr, CrLf is CHAR(13) + CHAR(10).

Is the rest of the email displayed using the expected HTML formatting? Can you view the source of the email and see that the expected HTML is being sent? Your problem seem to be with your HTML, not SQL, but if you send the relevant source HMTL, I'll take a peek.

Rick Krueger

Follow @dataogre

JimS-Indy SSCarpal Tunnel Points: 4514 More actions · Answer 2

Rick, here's the resulting HTML. I did fix it so it replaces CRLF and not just LF. It renders perfectly (except for the line break issue...) in Outlook. In gmail, the table body doesn't render, only the headers. Haven't figured that one out yet...

<html><head><style>td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:9pt;} </style></head><body><table cellpadding=0 cellspacing=0 border=0><tr bgcolor=#FFEFD8><td align=center><b>User Name</b></td><td align=center><b>Date</b></td><td align=center><b>Message</b></td></tr><tr><TD>dnicely</TD><TD>06/27/2012</TD><TD>Error Nbr -2147352567 - You entered an expression that has no value. --> CPASProd.Form_frmStaffAugDataEntry.Form_Current, #1, <br /> --> CPASProd.Form_frmStaffAugDataEntry.btnWorkerPost_Click, #10, </TD></tr><tr><TD>tbehm</TD><TD>06/27/2012</TD><TD>Total number of errors: 1 1. No current record. (#3021) </TD></tr><tr><TD>fcampbell</TD><TD>06/28/2012</TD><TD>User has version 12.0.6211.1000 installed.</TD></tr><tr><TD>fcampbell</TD><TD>06/28/2012</TD><TD>User has version 12.0.6211.1000 installed.</TD></tr><tr><TD>dnicely</TD><TD>06/28/2012</TD><TD>Total number of errors: 1 1. Table *splTimesheets* does not exist. (#3376) </TD></tr><tr><TD>dnicely</TD><TD>06/28/2012</TD><TD>Total number of errors: 1 1. Table *splTimesheets* does not exist. (#3376) </TD></tr><tr><TD>jowood</TD><TD>06/28/2012</TD><TD>Error Nbr -2147217871 - Timeout expired --> CPASProd.clsBillofMaterial.SQLAddBoMDetailItem, #30, <br /> --> CPASProd.clsBillofMaterial.AddItemtoBoM, #2, <br /> --> CPASProd.Form_frmItemMasterSelect.btnAddItem_Click, #8, </TD></tr><tr><TD>jowood</TD><TD>06/28/2012</TD><TD>Error Nbr -2147217871 - Timeout expired --> CPASProd.clsBillofMaterial.SQLAddBoMDetailItem, #30, <br /> --> CPASProd.clsBillofMaterial.AddItemtoBoM, #2, <br /> --> CPASProd.Form_frmItemMasterSelect.btnAddItem_Click, #8, </TD></tr><tr><TD>jowood</TD><TD>06/28/2012</TD><TD>Error Nbr -2147217871 - Timeout expired --> CPASProd.clsBillofMaterial.SQLAddBoMDetailItem, #30, <br /> --> CPASProd.clsBillofMaterial.AddItemtoBoM, #2, <br /> --> CPASProd.Form_frmItemMasterSelect.btnAddItem_Click, #8, </TD></tr><tr><TD>dnicely</TD><TD>06/28/2012</TD><TD>Total number of errors: 1 1. Table *splTimesheets* does not exist. (#3376) </TD></tr><tr><TD>dnicely</TD><TD>06/28/2012</TD><TD>Total number of errors: 1 1. Table *splTimesheets* does not exist. (#3376) </TD></tr><tr><TD>nfox</TD><TD>06/29/2012</TD><TD>Total number of errors: 1 1. Cannot append. An object with that name already exists in the collection. (#3367) </TD></tr><tr><TD>nfox</TD><TD>06/29/2012</TD><TD>Total number of errors: 1 1. Cannot append. An object with that name already exists in the collection. (#3367) </TD></tr><tr><TD>nfox</TD><TD>06/29/2012</TD><TD>Total number of errors: 1 1. Cannot append. An object with that name already exists in the collection. (#3367) </TD></tr><tr><TD>nfox</TD><TD>06/29/2012</TD><TD>Error Nbr 3075 - Syntax error (missing operator) in query expression *ProjectID=*. --> CPASProd.Form_frmWorkOrders.cbProjectID_AfterUpdate, #1, </TD></tr></table></body></html>

Jim

Rick Krueger SSC Eights! Points: 856 More actions · Answer 3

Your HTML looks solid to me and renders properly in Chrome and IE 9. The problem you are running into is on the email client side (Outlook and/or gmail) and since standards are weak there, it creates a lot of extra work for the developer. Email Design Guidelines[/url] was the best general reference I could find, yet doesn't solve your specific problem. Since it's not a SQL issue, I would recommend using Stack Overflow to get additional help (first search to see if the same question has already been answered there).

Rick Krueger

Follow @dataogre

JimS-Indy SSCarpal Tunnel Points: 4514 More actions · Answer 4

Thanks, Rick. I'll post over on Stack Overflow. I appreciate your time.

Jim