sqlserver sql转HTMM邮件发送
通过sql的形式，把表内数据通过邮件的形式发送出去
declare @title varchar(100)
DECLARE @stat_date CHAR(10),@create_time datetime
SET @stat_date=CONVERT(char(10),GETDATE(),120)
SET @create_time=DATEADD(MINUTE,-20,GETDATE())
DECLARE @xml NVARCHAR (max)
DECLARE @body NVARCHAR(MAX)
declare @tmp varchar(max)
set @tmp ='<table border = 1>'
set @tmp =@tmp+'<tr><td>dt<td>database_type<td>monidata<td>tag</tr>'select @tmp=@tmp+
'<tr style=''background-color:'+ case when tag='备份失败' then 'red' else '' end+'''>'
+'<td>'+ convert(varchar(100),dt, 120)
+'<td>'+ database_type
+'<td>'+ monidata
+'<td>'+ tag
+'</tr>'
from #temp2select @body='<html><body><H3>统一备份状态监控'+@stat_date+'</H3>'+ '<table border = 1>'+ @tmp+ '</table></body></html>'set @title='统一备份状态监控'+CONVERT(varchar(8),getdate(),112)Exec msdb.dbo.sp_send_dbmail @profile_name ='Monitor',@recipients='banzhenfei@xinchengyue.com;',@subject=@title,@body_format='HTML',@body = @body;
