How can I send plain text email (with line breaks) using sp_send_dbmail?

Question

I have a SQL Server 2008 procedure that sends email via sp_send_dbmail.

I'm using the following code:

  set @bodyText = ( select 
                      N'Here is one line of text ' +
                      N'It would be nice to have this on a 2nd line ' +
                      N'Below is some data: ' +
                      N' ' +
                      N' ' +
                      field1 +
                      N' ' +
                      field2 +
                      N' ' +
                      N'This is the last line'
                    from myTable )

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'myProfile',
        @recipients = @to,
        @body = @bodyText,
        @body_format = 'TEXT',
        @subject = 'Testing Email' ;

My myProfile is set to use the local smtp server, which results in a .EML file in c:\inetpub\mailroot\queue

When I open one of those .eml files (ug - the only thing that can open them is outlook express, looking at them in anything else just shows the body as a base64 encoded blob.) it looks like it's rendering the result as HTML - so I'm not sure if the problem is in the client, or

I've tried putting into the message, but that didn't work. How can I send plain text with line breaks, and verify that the end result looks correct?

BTW, I can't actually send the email to test it with real email clients - corp. network is locked down.

user_4560062 · Answer

In my case, the plain text email with CHAR(13)+CHAR(10) was getting formatted by Outlook that "helpfully" removes extra line breaks (unless it is unchecked in Outlook Options).

In addition, my list of entries would be dynamic, so I put together the STUFF FOR XML PATH that pulled in a concatenated string and simply added a literal empty string with a new line break and then assigned sp_send_dbmail's @body = @emailBody:

DECLARE @emailBody VARCHAR(MAX)

SET @emailBody = CONCAT('files: '
    ,(SELECT STUFF((SELECT
    ' 

' + CONCAT([FileName],'.csv')
    FROM localdb.dbo.tableName
    for xml path(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')))

How can I send plain text email (with line breaks) using sp_send_dbmail?

4 Answers