I'm trying to send myself a text message and I am unable to achieve line breaks in the message. I can do this in an email without issue using HTML, but it does not work the same for an SMS message.
DECLARE @TXT_LIST VARCHAR(255);
DECLARE @TXT_BODY NVARCHAR(MAX);
SET @TXT_LIST = 'myphonenumber@msg.fi.google.com';
SET @TXT_BODY = 'This should be line 1.';
-- SET @TXT_BODY += '\n';
-- SET @TXT_BODY += '\r\n';
-- SET @TXT_BODY += '<br />';
-- SET @TXT_BODY += '%0a';
SET @TXT_BODY += 'This should be line 2.';
EXEC msdb.dbo.sp_send_dbmail @profile_name='myProfile', @recipients=@TXT_LIST, @body_format='HTML', @subject='Test Subject', @body=@TXT_BODY;
I've tried all the methods you see above that are commented out and all of them show up as plain text on my phone and on one line instead of two.
The end goal is to run a query that returns X number of rows and have each line from the result set on a new line in the text message.
I stumbled upon the solution:
DECLARE @TXT_LIST NVARCHAR(255);
DECLARE @TXT_BODY NVARCHAR(MAX);
DECLARE @CRLF CHAR(2) = CHAR(13)+CHAR(10);
SET @TXT_LIST = 'myphonemumber@msg.fi.google.com';
SET @TXT_BODY = 'This should be line 1.';
SET @TXT_BODY += @CRLF;
SET @TXT_BODY += 'This should be line 2.';
EXEC msdb.dbo.sp_send_dbmail @profile_name='myProfile', @recipients=@TXT_LIST, @body_format='HTML', @subject='Test Subject', @body=@TXT_BODY;