sql server 发送邮件

— bi email

declare @cc varchar(10),@mail varchar(500), @str varchar(800),@year varchar(4),@month varchar(2);

declare @file_name varchar(50);

declare @mail_subject varchar(50);

declare @htmlbody varchar(max);

select @year = convert(varchar(4),datepart(year,dateadd(month,-1,getdate()))) ;

select @month = right(‘0’+convert(varchar(2),datepart(month,dateadd(month,-1,getdate()))),2);

select @mail=’bi@163.com.cn’;

 begin   set @htmlbody=

  n'<h1 style=”margin-left:30px; “><a href=”http://msbi.dcec.com.cn/pbreports”><font size=”4″>top 10 etl数据仓库抽取错误日志(点击查看明细)</font></a></h1>’ +

  n'<table border=”1″ style=”border:1px solid #d5d5d5;border-collapse:collapse;border-spacing:0;margin-left:30px;margin-top:20px;”>’ +

  n'<tr style=”height:25px;”><th width=60px” >表名</th>’+

  n'<th width=50px>包名</th><th width=120px>抽取时间</th>’+

  n'<th width=150px>错误信息</th>’+

  

  cast ( (

  select td=a.table_name, ”,td=a.package_name, ”,td=a.process_starttime,”, td=a.error_description, ”

  

  from (select top 10 table_name,package_name,process_starttime,error_description

   from tempdb.dbo.bi_etl_error ) a

  for xml path(‘tr’), type

  ) as nvarchar(max) ) +

  n'</table>’;

  –select @htmlbody;

  set @mail_subject=’数据仓库加载数据etl任务失败’;

  

  

  exec msdb.dbo.sp_send_dbmail

  @profile_name=’bimail’,

  @recipients=@mail,

  @subject=@mail_subject,

  @body=@htmlbody,

  –@execute_query_database=’reportserver’,

  –@query=’select * from dbo.roles’,

  –@attach_query_result_as_file=1,

  –@query_attachment_filename=n’a.xlsx’

  –@file_attachments=@file_name

  @body_format=’html’; 

  

 end