Improper Formatting when sp_send_dbmail ing into HTML

Question

I have taken the code from BOL on this subject under "C. Sending an HTML e-mail message" and created an email report that sends through code running out of a window.

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'<H1>Work Order Report</H1>' +
    N'<table border="1">' +
    N'<tr><th>Work Order ID</th><th>Product ID</th>' +
    N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
    N'<th>Expected Revenue</th></tr>' +
    CAST ( ( SELECT td = wo.WorkOrderID,       '',
                    td = p.ProductID, '',
                    td = p.Name, '',
                    td = wo.OrderQty, '',
                    td = wo.DueDate, '',
                    td = (p.ListPrice - p.StandardCost) * wo.OrderQty
              FROM AdventureWorks.Production.WorkOrder as wo
              JOIN AdventureWorks.Production.Product AS p
              ON wo.ProductID = p.ProductID
              WHERE DueDate > '2004-04-30'
                AND DATEDIFF(dd, '2004-04-30', DueDate) < 2 
              ORDER BY DueDate ASC,
                       (p.ListPrice - p.StandardCost) * wo.OrderQty DESC
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail @recipients='[e-mail]',
    @subject = 'Work Order List',
    @body = @tableHTML,
    @body_format = 'HTML' ;

 

I have made a few changes, like adding an a href link.  I use some <B></B> and <BR>s but nothing tricky.  The report shows customer feedback, so I am using a text field in the table for that.  But I have everything CASTed to NVARCHAR(MAX) and am using N in front of strings. 

 

The SP sends the email and it is 99% correct.  However, the results are varied.  On some emails, I get random < td> and < /td> tags (with the spaces in them) followed by a column being off.  I am using following code to help format things like my a href. 

 

SELECT @tableHTML = REPLACE(@tableHTML, '&lt;', '<')

SELECT @tableHTML = REPLACE(@tableHTML, '&gt;', '>')

SELECT @tableHTML = REPLACE(@tableHTML, '&amp;', '&')

 

The really strange part is that when I select @body before the send command and save it as html, the same < td> tags that are messed up with spaces in the email version are completely fine in the .html version.  So the HTML I am passing to the @body is perfect.  But random spaces are showing up in the email.  My test email, which will replicate the problem is about 27K characters, but I have read elsewhere that there is no max as @body uses NVARCHAR(MAX).

 

We are running SQL 2005 x64 with SP2.

 

Has anyone seen this or does anyone have suggestions?  Any help is greatly appreciated.

 

Thanks,

 

Jason

Answer 1

Did you get an answer to this issue?  I've got the same problems.

 

Answer 2

I haven't yet, but the project has been on hold.  I will be revisiting this next week and hopefully get some resolution.

Answer 3

I read this in a rush and am busy so scuse if nonsence. Have you checked data for NULLs, as this caused me a problem

I resolved by writing to a temp table and doing something like this.

UPDATE #ContractorAvailability SET ContactLastName = ' ' WHERE LEN(ContactLastName) = 0;

Once again sorry if talking nonsense.

Answer 4

UPDATE:  Actually this has nothing to do with SQL code, but it does have everything to do with Notification or sp_send_dbmail (or maybe the SMTP server).  A co-worker spotted that the really long HTML string was being cut periodically.  We looked at the HTML attachment and viewed source.  Despite turning off word wrap, there were "paragraphs" being shown.  We found that every 2093 spaces, the code has something in it that looks like a return in "view source" and looks like a space in the rendered document.

 

I would love it if someone could run the below and see what you get.  I get 2093 x's followed by a space and 7 more x's to make 2100 x's (and one annoying space).

 

DECLARE @thebody NVARCHAR(MAX)

SELECT @thebody = REPLICATE('x', 2100)

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Test',

@recipients = '[e-mail]',

@subject = 'Why 2093?',

@body = @thebody,

@body_format = 'HTML'

Thanks,

 

Jason

 

Answer 5

Okay, another UPDATE:

 

I only get this result when I send to my work account, which uses the First Class system.  When I do the above to my hotmail or yahoo accounts, it is just a straight up 2100 x's.  So I will continue to chase this down but it specifically seems to be a problem with First Class and not anything SQL/sp_send_dbmail. 

 

So you don't need to run the code because it won't have that exciting space at 2093...

 

Answer 6

I'm sending an email very similar to this and it works just fine when viewing the table from Outlook. However, with the increasing usage of receiving email from blackberry, it totally messed up the table formatting. In fact, it has no formatting AT ALL.  Is there any way I can format the table in blackberry format??

 

Thanks!

 

Answer 7

I'm having this same problem using sp_send_dbmail in SQL 2005.

It's really, really irritating. Has anyone come by a solution? This is also a tough one to google!

Answer 8

I read this in a rush and am busy so scuse if nonsence. Have you checked data for NULLs, as this caused me a problem

I resolved by writing to a temp table and doing something like this.

UPDATE #ContractorAvailability SET ContactLastName = ' ' WHERE LEN(ContactLastName) = 0;

Once again sorry if talking nonsense.

Could you give more detailed description? I'm new here.