Sending data from a database using send mail task in a table structure

Question

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

sumitagarwal781 Default port Points: 1493 More actions · Answer 1

Here is a example to send a mail with table formate.

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 AdventureWorks2008R2.Production.WorkOrder as wo

JOIN AdventureWorks2008R2.Production.Product AS p

ON wo.ProductID = p.ProductID

WHERE DueDate > '2006-04-30'

AND DATEDIFF(dd, '2006-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' ;

Wicked Old Hand Points: 356 More actions · Answer 2

Thank you for the answer.

There is one more thing.

I am using execute sql task for retrieving data from a table then

using foreach loop and a script task inside it to store the data from the rows in a variable.

Is it possible here to store the the data in a table format?

After that i use send mail task to send the email.