SQL中利用脚本创建database mail
编写人：CC阿爸
2014-6-14
多话不讲，请参考以下脚本
use master
go
exec sp_configure 'show advanced options',1
go
reconfigure
go
exec sp_configure 'Database mail XPs',1
go
reconfigure
go
DECLARE @ProfileName VARCHAR(255)
DECLARE @AccountName VARCHAR(255)
SET @ProfileName = 'SystemMail';
SET @AccountName = 'SystemMail';
--Initial Cleanup:
IF EXISTS(
SELECT * FROM msdb.dbo.sysmail_profileaccount pa
JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
WHERE
p.name = @ProfileName AND a.name = @AccountName)
BEGIN
PRINT 'Deleting Profile Account'
EXECUTE sysmail_delete_profileaccount_sp
@profile_name = @ProfileName,
@account_name = @AccountName
END
IF EXISTS( SELECT * FROM msdb.dbo.sysmail_profile p WHERE p.name = @ProfileName)
BEGIN
PRINT 'Deleting Profile.'
EXECUTE sysmail_delete_profile_sp
@profile_name = @ProfileName
END
IF EXISTS( SELECT * FROM msdb.dbo.sysmail_account a WHERE a.name = @AccountName)
BEGIN
PRINT 'Deleting Account.'
EXECUTE sysmail_delete_account_sp
@account_name = @AccountName
END
exec msdb..sysmail_add_account_sp
@account_name = 'SystemMail' -- 邮件帐户名称（SQL Server 使用）
,@email_address = 'potrontech@163.com' -- 发件人邮件地址
,@display_name = 'SystemMail' -- 发件人姓名
,@replyto_address = 'potrontech@163.com'
,@description = 'system alert'
,@mailserver_name = 'smtp.163.com' -- 邮件服务器地址
,@mailserver_type = 'SMTP' -- 邮件协议（SQL 2005 只支持SMTP）
,@port = 25 -- 邮件服务器端口
,@username = 'potrontech@163.com' -- 用户名
,@password = 'xxxxx' -- 此处为邮件密码。暂以x代替
,@use_default_credentials = 0
,@enable_ssl = 0
,@account_id = null
exec msdb..sysmail_add_profile_sp @profile_name = 'SystemMail' -- profile 名称
,@description = 'system alert' -- profile 描述
,@profile_id = null
exec msdb..sysmail_add_profileaccount_sp @profile_name = 'SystemMail' -- profile 名称
,@account_name = 'SystemMail' -- account 名称
,@sequence_number = 1 -- account 在profile 中顺序
------test databasemail
----DECLARE @xml NVARCHAR(MAX)
----DECLARE @body NVARCHAR(MAX)
------SET @xml =CAST(( SELECT ID AS 'td','',UserID AS 'td' ,'',UserName AS 'td' from ERV.dbo.CA FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
----SET @body ='<html><H1>Sales Reports</H1><body ><table border = 1><tr><td>ID</td><td>UserID</td><td>UserName</td></tr>'
----SET @body = @body + @xml +'</table></body></html>'
----
----
------test databasemail
----
----EXEC msdb.dbo.sp_send_dbmail
----@recipients=N'potrontech@163.com',
----@body= @body,
----@body_format ='HTML',
----@subject = 'Test Database Mail html',
----@profile_name = 'SystemMail'
----go
----use msdb
----
------要检查消息是否发送成功，我可以对sysmail_allitems系统视图执行一次查询。
--select * from sysmail_allitems
--select * from sysmail_mailitems
--select * from sysmail_event_log
--将一个配置文件设置为数据库的默认公共配置文件
exec msdb.dbo.sysmail_help_principalprofile_sp
EXECUTE msdb.dbo.sysmail_update_principalprofile_sp
@principal_name = 'public',
@profile_name = 'SystemMail',
@is_default = '1';
有关更多的技术分享，大家可以加入我们的技术群。
欢迎加入技术分享群：238916811