-- BI EM
aiL
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"
" +
N"
" +
N"表名 | "+
N"包名 | 抽取时间 | "+
N"错误信息 | "+
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"
---|
";
--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_f
ORMat="HTML";
end
0