SQLserver发送邮件

淩亂°似流年 2022-06-18 12:00 199阅读 0赞

\--server 配置  
sp\_configure 'show advanced options', 1  
go  
RECONFIGURE  
go  
sp\_configure 'SQL Mail XPs', 1  
go  
RECONFIGURE  
go

sp\_configure 'Database Mail XPs', 1  
go  
RECONFIGURE  
go

\--1　基本配置ファイルを作成  
EXECUTE msdb.dbo.sysmail\_help\_profile\_sp  
\--2　アカウントを作成  
EXECUTE msdb.dbo.sysmail\_help\_account\_sp ;  
\--3 基本配置ファイルとアカウント結合し順番を指定  
EXECUTE msdb.dbo.sysmail\_help\_profileaccount\_sp  
\--4 既定のアカウントを指定  
EXECUTE msdb.dbo.sysmail\_help\_principalprofile\_sp ;  
\--5 システムパラメータを指定  
EXECUTE msdb.dbo.sysmail\_help\_configure\_sp ;

exec dbo.sp\_send\_dbmail  
@profile\_name='myMail',  
@recipients='XXX@163.com',  
@subject='メールテスト',  
@body='hello'

exec dbo.sp\_send\_dbmail  
select \* from sysmail\_log  
select \* from sysmail\_sentitems

use master  
go  
exec sp\_configure 'show advanced options',1  
go  
reconfigure  
go  
exec sp\_configure 'Database mail XPs',1  
go  
reconfigure  
go

\--1　基本配置ファイルを作成  
EXECUTE msdb.dbo.sysmail\_help\_profile\_sp '1','fan'  
\--2　アカウントを作成  
EXECUTE msdb.dbo.sysmail\_help\_account\_sp ;  
\--3 基本配置ファイルとアカウント結合し順番を指定  
EXECUTE msdb.dbo.sysmail\_help\_profileaccount\_sp  
\--4 既定のアカウントを指定  
EXECUTE msdb.dbo.sysmail\_help\_principalprofile\_sp ;  
\--5 システムパラメータを指定  
EXECUTE msdb.dbo.sysmail\_help\_configure\_sp

exec msdb.dbo.sysmail\_add\_profile\_sp 'fan',''

EXECUTE msdb.dbo.sysmail\_add\_account\_sp  
    @account\_name = 'fan\_mail',  
    @email\_address = ['xxx@dl.cn'][xxx_dl.cn],  
    @display\_name = 'fan',  
    @mailserver\_name = 'mail.dl.cn',  
    @username= ['xxx@dl.cn'][xxx_dl.cn],  
    @password ='fh13084178517',  
    @use\_default\_credentials ='0';

EXECUTE msdb.dbo.sysmail\_add\_profileaccount\_sp 1,null,1,null,1

EXECUTE msdb.dbo.sysmail\_add\_principalprofile\_sp 1,null,1,null,1

EXEC msdb.dbo.sp\_send\_dbmail  
 @profile\_name = 'fan',  
@recipients = ['xxx@dl.cn'][xxx_dl.cn],  
@subject = 'myTest',  
@query = 'select \* from test.dbo.a',  
@attach\_query\_result\_as\_file = 1,  
@query\_attachment\_filename = 'a.txt',  
@body='my love ,hhhhh'

exec msdb.dbo.sp\_send\_dbmail  
select \* from msdb.dbo.sysmail\_log  
select \* from msdb.dbo.sysmail\_sentitems

select \* from sysmail\_log  
select \* from sysmail\_sentitems

EXEC msdb.dbo.sp\_send\_dbmail  
 @profile\_name = 'fan',  
@copy\_recipients='xxx@163.com',  
@recipients = ['xxx@dl.cn'][xxx_dl.cn],  
@blind\_copy\_recipients [='xxx@vip.163.com'][xxx_vip.163.com],  
@subject = 'myTest',  
@file\_attachments = 'c:\\5.txt.txt;c:\\a\\5.txt.txt',  
@body='my love ,hhhhh',  
@importance = 'High'

[xxx_dl.cn]: mailto:'%20rel=
[xxx_vip.163.com]: mailto:='%20rel=