你的位置:首页 > 数据库

[数据库]SQL中利用脚本创建database mail.


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 @

----DECLARE @body NVARCHAR(MAX)

------SET @

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

----

----

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