How to Send Email From SQL Server
By FoxLearn 9/16/2020 4:48:56 AM 4.92K
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
Right-clicking on Database Mail
then select Configure Database Mail
Clicking Next button.
Selecting option Set up Database Mail by performing the following tasks, then click Next button.
Selecting Yes button.
Clicking Add button
sql server email security
Entering your email information.
Don't forget to tick 'This server requires a secure connection (SSL)
sql server database mail profile
Entering your profile name, then click Next button.
Selecting your profile, then click Next button.
Clicking Next button.
Clicking Finish button.
Clicking Close button to finish.
Right-clicking on Database Mail, then enter your email need to send.
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='[email protected]', @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”.
Here are some details about the SMTP server for your reference.
Mail Account | SMTP Server | Port |
Gmail | smtp.gmail.com | 587 |
Hotmail | smtp.live.com | 587 |
Yahoo | smtp.mail.yahoo.com | 25 |
AOL | smtp.aol.com | 587 |
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'
You need to click enable it to allow you to send email from another software.
- How to Download and Restore Northwind database to SQL Server
- How to use SQL INSERT INTO SELECT Statement
- Data type SQL Server
- SQL Server Migration Assistant
- How to drop table if exists in sql server
- How to fix 'The specified directory for the INSTALLSHAREDDIR parameter is not valid'
- How to get all table names of a particular database using TSQL
- How to use ROW_NUMBER Function in SQL Server