SQL Email Formatting disappearing CR LF

Question

I have a TSQL script that selects data and generates an email. My script is quite complex but I have cut it down to a simple example to demonstrate my problem.

In simple terms when one of my fields go over a particular length (21 characters) the CR and LF I have inserted into the VAR_Body variable go missing. The text from the field is not truncated.

Expected Result

Renewal Date= 23 Jun 2017
Existing Insurer= 1234567890123456789012
Existing Insurer ID= 6007

Actual Result

Renewal Date= 23 Jun 2017
Existing Insurer= 1234567890123456789012 Existing Insurer ID= 6007

If I take one character of the Existing Insurer string it works.

Here is my cut down code to demo the problem.

Does anyone have any ideas what is going on here?

I have run out of things to try.

Thanks

David

-- Email Variables
Declare @VAR_Dest_EmailAddress varchar(150)
set @VAR_Dest_EmailAddress = 'myemail@email.com.au'
Declare @VAR_Subject varchar(100)
Declare @VAR_Body  varchar(1000)

-- Format Variables
Declare @DateDisplayFormat int
Set @DateDisplayFormat = 106 -- DD MMM YYYY eg 25 MAY 2017
Declare @MoneyDisplayFormat int
set @MoneyDisplayFormat = 1  -- Commas every three digits and two digits to the right of the decimal point.


-- Data variables
Declare @sPlanNumber varchar(10)
Declare @dRenewal datetime
Declare @sExistingInsurer varchar(50)
Declare @lExistingInsurerID int

-- Set the Variables
Set @sPlanNumber = '12345'
Set @dRenewal = '2017-06-23 00:00:00.000'
set @sExistingInsurer = '1234567890123456789012'
set @lExistingInsurerID = '6007'

-- Set the body of the email
    set @VAR_Body = 'Renewal Date= ' + Convert(varchar(11),@dRenewal,@DateDisplayFormat) + CHAR(13)+ CHAR(10) 
    set @VAR_Body = @VAR_Body + 'Existing Insurer= ' + @sExistingInsurer + CHAR(13) + CHAR(10)
    set @VAR_Body = @VAR_Body + 'Existing Insurer ID= ' + Convert(varchar(10),@lExistingInsurerID) + CHAR(13) + CHAR(10)

-- Send the email
EXEC msdb.dbo.sp_send_dbmail 
    @recipients = @VAR_Dest_EmailAddress,
    @subject = @sPlanNumber,
    @body = @VAR_BODY,
    @profile_name = 'SQL Email Profile' ;

Answer 1

If you have no requirement around email format (TEXT vs HTML) you could use HTML formatting for your email. You can then use <br/> and other html tags to format the email.

EXEC msdb.dbo.sp_send_dbmail 
    @recipients = @VAR_Dest_EmailAddress,
    @subject = @sPlanNumber,
    @body = @VAR_BODY,
    @body_format = 'HTML' --declare the body formatting of the email to be HTML
    @profile_name = 'SQL Email Profile' ;

You can then format your email :

set @VAR_Body = 'Renewal Date= ' + Convert(varchar(11),@dRenewal,@DateDisplayFormat) + '<br/>' 
--we can now use the html break character which will be rendered by all email clients
set @VAR_Body = @VAR_Body + 'Existing Insurer= ' + @sExistingInsurer + '<br/>'
set @VAR_Body = @VAR_Body + 'Existing Insurer ID= ' + Convert(varchar(10),@lExistingInsurerID) + '<br/>'

Reference can be found in the MSDN docs here