当前位置：首页 > article >正文

sqlserver sql转HTMM邮件发送

article 2025/1/7 14:13:02

通过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  #temp2

		select @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;

在这里插入图片描述