SQL Create send mail account

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 = ' [email protected] '

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 [Email protected]_profile_name

if exists (SELECT * from Msdb.dbo.sysmail_account WHERE [email protected]_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 [email protected]_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 [email protected]_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 [email protected]_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 [Email protected]_email_address,

@display_name = @my_profile_name,

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

EXEC msdb.dbo.sysmail_add_profile_sp

@profile_name [Email Protected]_profile_name,

@description = ' profile used for Database Mail '

[Email protected]_id = @my_profile_ID OUTPUT

SET SELECT @my_sequence_number =profile_id from msdb. Sysmail_profile WHERE [Email protected]_profile_name

SET SELECT @my_account_ID =account_id from Msdb.dbo.sysmail_account WHERE [email protected]_account_name

EXEC msdb.dbo.sysmail_add_profileaccount_sp

--@[email protected]_profile_id,

--@[email protected]_account_id,

@profile_name = @my_profile_name,--profile name

@account_name = @my_account_Name,--account name

@sequence_number = @my_sequence_number--Order of account in profile

EXEC msdb.dbo.sysmail_add_principalprofile_sp

@profile_name = @my_profile_name,

@principal_name [Email Protected]_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 = ' [email protected] ',

@subject = ' Test1 ',

@Body = ' Test '

*/

End

SQL Create send mail account