Solved

format @body in sp_send_dbmail

Posted on 2009-04-07
4
1,517 Views
Last Modified: 2012-06-27
I am calling sp_send_dbmail from within a stored procedure.  I am getting the content of @body from a field in a database.  I would like the body of the message to be formatted with as though the email had been typed.  I need to have the proper line feeds, bolding etc.  Does anyone know how to do this?

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLServerDatabaseMailProfile',
@recipients = @recipients,
@body = @mymsg,
@subject = 'my subject'
0
Comment
Question by:spmcmorrow
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 11

Accepted Solution

by:
aaronakin earned 500 total points
ID: 24092196
You'll need to format the body as HTML and add @body_format = 'HTML' to the procedure.
0
 

Author Comment

by:spmcmorrow
ID: 24097678
Ok, I just need a few details cleared up. I need to store the body of the message in a field (ideally I would like the user to be able to edit that message body..but that is second)  I need to take that message and customize it based on my data.  I should be able to do that.  

But what type of field in MS SQL do I store the html in.  I tried entering it in a varchar field and it didn't like all the special characters.  
0
 
LVL 11

Expert Comment

by:aaronakin
ID: 24098198
Varchar field should be fine.  The script below will give you an example.

CREATE TABLE #Test (HtmlText VARCHAR(5000))
 
INSERT INTO #Test VALUES ('<html><body><p>This is html text</p></body></html>')
 
SELECT HtmlText FROM #Test
 
DROP TABLE #Test

Open in new window

0
 

Author Closing Comment

by:spmcmorrow
ID: 31567742
Thank you.  You answered the question I asked.  Now I have a few more, but I can ask them separately.  Thanks.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question