How can I send plain text email (with line breaks) using sp_send_dbmail?
You aren't actually inserting any line breaks. You can embed them directly in a string literal in SQL Server as below.
SET @bodyText = (SELECT N'Here is one line of text
It would be nice to have this on a 2nd line
Below is some data:
' + field1 + N'
' + field2 + N'
' + N'This is the last line'
FROM myTable);
Or a tidier approach might be
DECLARE @Template NVARCHAR(max) =
N'Here is one line of text
It would be nice to have this on a 2nd line
Below is some data:
##field1##
##field2##
This is the last line';
SET @bodyText = (SELECT REPLACE(
REPLACE(@Template,
'##field1##', field1),
'##field2##', field2)
FROM myTable);
Both will raise an error if myTable
contains more than one row as you are assigning the result to a scalar variable.
I've always used CHAR(13)+CHAR(10)
to create line breaks (which seems to work mixed in with nvarchar values) in TSQL, so try something like this:
DECLARE @CRLF char(2)
,@bodyText nvarchar(max)
,@field1 nvarchar(10)
,@field2 nvarchar(10)
SELECT @CRLF=CHAR(13)+CHAR(10)
,@field1='your data'
,@field2='and more'
set @bodyText =
N'Here is one line of text '
+@CRLF+ N'It would be nice to have this on a 2nd line '
+@CRLF+ N'Below is some data: ' + N' ' + N' ' + ISNULL(@field1,'') + N' ' + ISNULL(@field2 + N' ' ,'')
+@CRLF+ N'This is the last line'
PRINT @bodyText
OUTPUT:
Here is one line of text
It would be nice to have this on a 2nd line
Below is some data: your data and more
This is the last line
this CHAR(13)+CHAR(10)
will work with msdb.dbo.sp_send_dbmail
, I send formatted e-mails using that all the time.