sqlserver发送邮件

  • Z时代
  • 2024-01-10
  • 分类：综合

database

-- 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

 

以上是 sqlserver发送邮件 的全部内容， 来源链接： utcz.com/z/531886.html

回到顶部