Use MSDB
Go
Declare @ profilename varchar (255)
Declare @ accountname varchar (255)
Declare @ smtpaddress varchar (255)
Declare @ emailaddress varchar (128)
Declare @ displayuser varchar (128)
Set @ profilename = 'dbmailprofile ';
Set @ accountname = 'dbmailaccount ';
Set @ smtpaddress = 'mail .yoursmtpserver.com ';
Set @ emailaddress = 'dbmail @ yoursmtpserver.com ';
Set @ displayuser = 'the mail man ';
-- Deleting profile account, if exists profile account
If exists (select 1
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
Execute sysmail_delete_profileaccount_sp @ profile_name = @ profilename,
@ Account_name = @ accountname
End
-- Deleting profile, if exists Profile
If exists (select 1
From MSDB. DBO. sysmail_profile P
Where p. Name = @ profilename)
Begin
Execute sysmail_delete_profile_sp @ profile_name = @ profilename
End
-- Deleting account, if exists account
If exists (select 1
From MSDB. DBO. sysmail_account
Where a. Name = @ accountname)
Begin
Execute sysmail_delete_account_sp @ account_name = @ accountname
End
-- Create account
Execute MSDB. DBO. sysmail_add_account_sp @ account_name = @ accountname,
@ Email_address = @ emailaddress, @ display_name = @ displayuser,
@ Mailserver_name = @ smtpaddress
-- Create profile
Execute MSDB. DBO. sysmail_add_profile_sp @ profile_name = @ profilename
-- Create profile account
Execute MSDB. DBO. sysmail_add_profileaccount_sp @ profile_name = @ profilename,
@ Account_name = @ accountname, @ sequence_number = 1;
-- Turn on Database mail XPS, if the configuration is turn off
Sp_configure 'show advanced options', 1;
Go
Reconfigure;
Go
Sp_configure 'database mail XPS ', 1;
Go
Reconfigure
Go
-- Test send email
Exec MSDB. DBO. sp_send_dbmail @ recipients = 'test @ gmail.cn ',
@ Body = 'test email body', @ subject = 'test email subobject ',
@ Profile_name = 'dbmailprofile'
-- Search the result that send email
Select * From MSDB. DBO. sysmail_allitems