sql创建发送邮件账号

ALTER PROCEDURE dbo.sx_pro_autoEmail_account

AS

begin

 

 

Declare @my_profile_name varchar(50)

Declare @my_account_Name varchar(50)

Declare @my_principal_name varchar(50)

Declare @my_email_address varchar(100)

 

SET @my_profile_name='DB_AutoMail'

SET @my_account_Name='HK'

SET @my_principal_name='public'   --'sxquadb'  -- db_name()

SEt @my_email_address= 'chengjun@safrangroup.cn'

 

 

Declare @my_profile_ID int

Declare @my_sequence_number int

Declare @my_account_ID int

SET SELECT @my_profile_ID=profile_id FROM msdb..sysmail_profile WHERE name=@my_profile_name

 

 

 

if exists(SELECT * FROM msdb.dbo.sysmail_account WHERE name=@my_account_Name)

   begin

    EXECUTE msdb.dbo.sysmail_delete_account_sp

    @account_name = @my_account_Name

   end

 

if exists(SELECT * FROM msdb.dbo.sysmail_profile WHERE name=@my_profile_name)

   begin

     EXECUTE msdb.dbo.sysmail_delete_profile_sp

     @profile_name = @my_profile_name,@force_delete = 0;

   end

 

if exists(SELECT * FROM msdb.dbo.sysmail_profileaccount WHERE profile_id=@my_profile_ID)

   begin

    EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp

    @profile_name = @my_profile_name,

    @account_name = @my_account_Name;

   end

 

if exists(SELECT * FROM msdb.dbo.sysmail_principalprofile WHERE profile_id=@my_profile_ID)

   begin

    EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp

    @principal_name = @my_principal_name,

    @profile_name = @my_profile_name;

   end

 

 

EXEC msdb.dbo.sysmail_add_account_sp 

    @account_name = @my_account_Name, 

    @description = 'Mail account for database e-mail.', 

    @email_address =@my_email_address, 

    @display_name = @my_profile_name, 

    @mailserver_name = 'suz-smtp.crd1.crf1';

 

 

EXEC msdb.dbo.sysmail_add_profile_sp

       @profile_name =@my_profile_name,

       @description = 'Profile used for database mail'

       --@profile_id = @my_profile_ID OUTPUT

 

SET SELECT @my_sequence_number=profile_id FROM msdb..sysmail_profile WHERE name=@my_profile_name

SET SELECT @my_account_ID=account_id FROM msdb.dbo.sysmail_account WHERE name=@my_account_Name

 

 

EXEC msdb.dbo.sysmail_add_profileaccount_sp  

    -- @profile_id=@my_profile_ID,

    -- @account_id=@my_account_ID,

     @profile_name = @my_profile_name,   -- profile 名称    

     @account_name = @my_account_Name,     -- account 名称      

     @sequence_number = @my_sequence_number  -- account 在 profile 中顺序

 

 

EXEC msdb.dbo.sysmail_add_principalprofile_sp

    @profile_name = @my_profile_name,

    @principal_name =@my_principal_name,

    @is_default = 0;

 

 

--select * from  msdb.dbo.sysobjects

--select * from  msdb..sysmail_profile

--SElect * FROM msdb.dbo.sysmail_account

--SElect * FROM msdb.dbo.sysmail_principalprofile

--SELECT * FROM msdb.dbo.sysmail_profileaccount

 

 

 

/*

EXEC msdb.dbo.sp_send_dbmail

   @profile_name ='DB_AutoMail',   -- 'DB_AutoMail',  --'SXAC_OTHER_DBMAIL',

   @recipients = 'chengjun.Wang@safrangroup.cn',

   @subject = 'Test1',

   @Body='Test'

 

*/

 

 

 

end

转载于:https://www.cnblogs.com/3xin/p/8026817.html