July 5, 2011 at 1:25 am
HI All
We have a task which sends and email and now the email has got to much to read on one line.
I want to put in carrage returns or line feeds at certain points in the code but when I test the email, it doesnt seem to have put them in as its still all on the same line.
I have tried the following
SET @Mess = 'string1' + char(13) + 'string2' + char(13) + 'string3'
SET @Mess = 'string1'
SET @Mess = @Mess + 'string 3'
exec msdb.dbo.sp_send_dbmail @body = @Mess
Can you please advise.
Thanks
Ant
July 5, 2011 at 2:15 am
it just seems to be sp_send_dbmail which is missing the char(13)'s
if you export the output of @Mess to text in SSMS it shows on new lines which is great
just the mail wont display it how i want it to be displayed
i have tried @body_format = 'text' in the mail parameters but still no joy.
July 5, 2011 at 3:19 am
ok had a brain wave and it works to an extent
changed the body format to html, put in '< br >' instead of char(13) to simulate a HTML carrage return
this displays as needed on the email
just strange how sp_send_dbmail doesnt take into account the char(13)'s
edit: forgot if you put html tags in a post it takes them into account, modified the tag
July 5, 2011 at 3:41 am
July 5, 2011 at 3:59 am
Back in the halcyon days of DOS :hehe: etc., CHAR(13) was the carriage return control character - and did just that, i.e. returned the carriage to the start of the current line!
If you wanted to drop to the next line, CHAR(10) was used.
A combination of CHAR(13) + CHAR(10) was necessary to start a new paragraph on the next line.
Might be worth a try ???
The impossible can be done at once, miracles may take a little longer 🙂
July 5, 2011 at 4:06 am
yeah i have tried both char(10), char(13) and char(10)+char(13), they all individually do what is needed when you look at the results in a text output but not in dbmail, I am going on the conculsion that it is seeing them as white space and removing them from the mail.
July 6, 2011 at 8:43 am
Hi Anthony,
Im assuming that you are working in SQL2008. I've experienced this problem before and discovered that in SQL2000, char(10) was the way to put text on a new line with an email.
Since then, working in both SQL2005 and SQL2008 r2, it seems that you don't need the char(10). You can simply write the body as you want it to look.
Try the following
declare @Mess varchar(2000)
declare @string1 varchar(150) =
'This is the first string.'
declare @string2 varchar(150) =
'This is the second string.'
declare @string3 varchar(150) = 'This is the third string.'
set @Mess = @string1
exec msdb.dbo.sp_send_dbmail @profile_name='YOUR PROFILE', @recipients = 'YOUR EMAIL', @subject = 'subject', @body = @Mess
The email body from the above will look like this.
This is the first string.This is the second string.This is the third string.
Now, run the below code
declare @Mess varchar(2000)
declare @string1 varchar(150) =
'This is the first string.
'
declare @string2 varchar(150) =
'This is the second string.
'
declare @string3 varchar(150) = 'This is the third string.'
declare @string4 varchar(100) = '
1
2
3
4
5
.
.'
set @Mess = @string1
exec msdb.dbo.sp_send_dbmail @profile_name='YOUR PROFILE', @recipients = 'YOUR EMAIL', @subject = 'subject', @body = @Mess
As you can see from above, just put whatever you want on a new line when building the @Mess string
HTH :-):-)
taybre
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply