Ways to send HTML-formatted messages in SQL Server

Sqlsever Configure the method of the mailbox do not say, Baidu search by itself. e-Mail can be very convenient to help operators to do statistics or send to customers customized orders and so on.

The following is a list of 3 examples of sending mail using SQL Server:

A. Sending an e-mail

This example sends an e-mail message to Dan Wilson using an email address [email protected] . The subject of the message is automated Success message. The message body contains a word ' The stored procedure finished successfully '.

EXEC msdb.dbo.sp_send_dbmail@profile_name = ' AdventureWorks Administrator ', @recipients = ' [email protected] ', @body = ' The stored procedure finished successfully. ', @subject = ' automated Success Message ';

B. Sending an e-mail with query results

This example sends an e-mail message to Dan Wilson using an email address [email protected] . The subject of the message is Work order Count. Database Mail attaches the result as a text file.

EXEC msdb.dbo.sp_send_dbmail@profile_name = ' AdventureWorks Administrator ', @recipients = ' [email protected] ', @query = ' SELECT COUNT (*) from AdventureWorks.Production.WorkOrderWHERE duedate > ' 2004-04-30 '  and DATEDIFF (DD, ' 2004-04-30 ", DueDate) < 2 ', @subject = ' work Order Count ', @attach_query_result_as_file = 1;

C. Sending HTML e-mail This example uses an e-mail address[email protected]Send an email to Dan Wilson. The subject of the message isWork Order Listand contains an HTML document, Database Mail sends the message in HTML format. DECLARE @tableHTML NVARCHAR (MAX);
SET @tableHTML =
N ' 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 descfor XML PATH (' tr '), TYPE) as NVARCHAR (MAX)) +n ' </table> '; EXEC msdb.dbo.sp_send_dbmail @recipients = ' [email protected] ',
@subject = ' work Order List ',
@body = @tableHTML,
@body_format = ' HTML '; The above examples are tested to be feasible.

Ways to send HTML-formatted messages in SQL Server