--直接发文本
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ZuLin',
@recipients = '8711@gs4.comp',
@body='test',
@subject = '数据库邮件测试'
--带查询结果
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ZuLin',
@recipients = '8711@gs4.comp',
@query='select getdate()',
@subject = '数据库邮件测试'
@attach query result as file =1;
--以HTML发送
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Work Order Report</H1>' +
N'<table cellSpacing=1 cellPadding=3 width="96%" align=center border=0>' +
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 = '1', '',
td = '2', '',
td = '3', '',
td = '4', '',
td = '5', '',
td = '6'
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail @recipients='8711@gs4.comp',
@profile_name='ZuLin',
@subject = '数据库邮件测试',
@body = @tableHTML,
@body_format = 'HTML' ;