We can configure the gmail in our SQL server to send the emails to report the problems in the SQL server. We can configure any email services to SQL Server. All we need is information about the SMTP server name, Port number and whether email services using SSL connection or not. Following steps are used to configure email in SQL server.
1. Check with “Sp_Configure”. You will notice that config_values and run_Values as 0. It means that email service from sql server is not enabled.
2. Run the following Script to enable the email services in sql server
sp_CONFIGURE 'show advanced', 1 GO RECONFIGURE GO sp_CONFIGURE 'Database Mail XPs', 1 GO RECONFIGURE GO
3. Connect to SQL server database engine and go to Management
4. Right click on the “Database Mail” and click “Configure Database Mail”
5. You will be shown up with the following wizard. Just skip that by clicking “Next”
6. Select “Manage Database Mail Accounts and Profiles” and click “Next”
7. Select “Create Profile” and click “Next”
8. Enter the Profile Name and Description
9. Click “Add” to Add SMTP accounts
10. You will be prompted with the following screen and Add your credentials. For gmail use the following as per screenshot.
i. email Address: email@example.com
ii. Display Name: Your name
iii. Server name: smtp.gmail.com
iv. Port Number: 587 [Important note : if you do internet search to find out the port number, they refer with 465, but sql server is not recognizes this port. So use 587]
v. Check this server uses the SSL Connection
vi. In SMTP Authentication, Select “Basic Authentication”. Provide your Gmail username and password.
11. You will be shown up with the following screenshot. Click “Next”
12. You will get the success message
13. Click close
To Test the email is working fine:
1. Go to Management
2. Right click on the “Database Mail”
3. Click “Send Test E – mail…..”
4. Login to your gmail account and check you received an email from your sql server.
Queries to verify to check the status of the email:
USE msdb ; GO SELECT * FROM sysmail_allitems SELECT * FROM sysmail_sentitems SELECT * FROM sysmail_unsentitems SELECT * FROM sysmail_faileditems --To find out failed mail items with description on it SELECT items.subject, items.last_mod_date ,l.description FROM dbo.sysmail_faileditems as items INNER JOIN dbo.sysmail_event_log AS l ON items.mailitem_id = l.mailitem_id
Hope this helps