Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
yadhu_cse
Constraint Violating Yak Guru
252 Posts |
 Posted - 2010-11-08 : 00:41:16
|
| Hi,I want to send table data in html format. can any one tel how to do it using sP_send_mail |
|
|
bhawana
Starting Member
4 Posts |
Posted - 2010-11-08 : 04:18:49
|
| Pasting some code below as an example. Might be of some help.DECLARE @tableHTML NVARCHAR(MAX) ;SET @tableHTML = N'<H1>Work Order Report</H1>' + N'<table border="1">' + N'<tr><th>Work Order ID</th><th>Product ID</th>' + N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' + N'<th>Expected Revenue</th></tr>' + CAST ( ( SELECT td = wo.WorkOrderID, '', td = p.ProductID, '', td = p.Name, '', td = wo.OrderQty, '', td = wo.DueDate, '', td = (p.ListPrice - p.StandardCost) * wo.OrderQty FROM AdventureWorks.Production.WorkOrder as wo JOIN AdventureWorks.Production.Product AS p ON wo.ProductID = p.ProductID WHERE DueDate > '2004-04-30' AND DATEDIFF(dd, '2004-04-30', DueDate) < 2 ORDER BY DueDate ASC, (p.ListPrice - p.StandardCost) * wo.OrderQty DESC FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' ;EXEC msdb.dbo.sp_send_dbmail @recipients='danw@Adventure-Works.com', @subject = 'Work Order List', @body = @tableHTML, @body_format = 'HTML' ; |
 |
|
|
yadhu_cse
Constraint Violating Yak Guru
252 Posts |
Posted - 2010-11-08 : 07:12:10
|
| hi, colud u tel me how to bring values to next lineSET @tableHTML =N'<H1>Dear sir</H1>'+N'<table border="1">'+N'<tr><th>SeqNo</th>Body<th></th></tr>'+CAST((select td=seqNo ,td=emailsbody from emailsTosend where Seqno=@minSeqno) AS VARCHAR(100))+N'</TABLE>'for above query m getting follwowing error. how get two coloumn values from table. |
|
|
|
|
|
|
|
|
HTML FORMATTING