How to create an SQL Server database email

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.