大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说sqlserver发送邮件给多人_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
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
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/10378.html
