How to Send Email From SQL Server

This post shows you how to send an email from SQL Server using SMTP Server.

You can easily send an E-mail in SQL Server using SMTP Server. First, You need to configure database mail as shown below.

Opening your Microsoft SQL Server Management Studio, then login to your sql server -> select Management item.

How to configure database mail in SQL Server

send email from sql server

Right-clicking on Database Mail

send email from sql server

then select Configure Database Mail

send email from sql server

Clicking Next button.

send email from sql server

Selecting option Set up Database Mail by performing the following tasks, then click Next button.

send email from sql server

Selecting Yes button.

send email from sql server

Clicking Add button

sql server email security

send email from sql server

Entering your email information.

Don't forget to tick 'This server requires a secure connection (SSL)

sql server database mail profile

send email from sql server

Entering your profile name, then click Next button.

send email from sql server

Selecting your profile, then click Next button.

send email from sql server

Clicking Next button.

send email from sql server

Clicking Finish button.

send email from sql server

Clicking Close button to finish.

send email from sql server

Right-clicking on Database Mail, then enter your email need to send.

send email from sql server

Clicking OK button, then check your mail box.

You can also run the sql script below to configure database mail.

sp_CONFIGURE 'show advanced', 1 
GO 
RECONFIGURE 
GO 
sp_CONFIGURE 'Database Mail XPs', 1 
GO 
RECONFIGURE 
GO 

Running the sql script above and you will get the following results.

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Database Mail XPs' changed from 1 to 1. Run the RECONFIGURE statement to install.

Completion time: 2020-09-16T14:46:21.8130404+07:00

sql server send email from stored procedure

If you want to send an email from stored procedure you can runt the sql script below.

USE msdb
GO
EXEC sp_send_dbmail @profile_name='lucy',
@recipients='tanhynh@gmail.com',
@subject='Test message',
@body=N'Welcome to FoxLearn...'

You can verify the email status, whether it sent successfully or not, and get other information using the query below:

use msdb  
go  
select * from sysmail_allitems  

If you want to view mail log, you can right-click on Database Mail, then select “View database Mail Log”.

View database Mail Log

Here are some details about the SMTP server for your reference.

Mail AccountSMTP ServerPort
Gmailsmtp.gmail.com587
Hotmailsmtp.live.com587
Yahoosmtp.mail.yahoo.com25
AOLsmtp.aol.com587

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2020-09-16T14:50:18). Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.7.0 Authentication Required

If you got an error above when sending an email, you should login to your google mail acount, then check 'Less secure app access'

less secure app access

You need to click enable it to allow you to send email from another software.

less secure app access