SQL Server Database Mail is used to send e-mail from the SQL Server Database Engine to user. You can also send an output of any stored procedures or table records in e-mail as an excel/text attachment or in HTML format in e-mail body.
Lets configure SQL Server Database Mail.
To Configure Database Mail there are three steps as following.
- Creating a Database Mail account.
- Creating a Database Mail profile.
- Link mail account with profile.
Above three step can be done either by using Database Mail Configuration Wizard, or T-SQL code.
Configuration using Database Mail Configuration wizard
First go to object Explorer > Then expand Management Folder > Right Click on Database Mail.
Next, Right Click on Configure Database Mail.
After clicking on Configure Database Mail , a Database Mail Configuration Wizard window opens then click on Next button.
As you are configuring Database Mail for first time so, select first option Set up Database Mail…
> then click on Next button.
After clicking on Next button, a New profile window opens.
Now you nee to setup a Database Mail profile and Account as shown in below screenshot.
In Profile Name andย Description : Specify name for Database Mail profile and Description. Description is an optional.
After providing profile name and Description, Click on Add.. button to set up account and link it with profile.
Once you click on Add button, a Add Account to Profile window opens, then click on New Account button.
After clicking on New Account button, a window opens where you need to specify details for new account that will be linked with the profile.
In Account name and Description : Specify name for account and account description, account description is an optional.
In Outgoing Mail Server(SMTP) : Set up outgoing Mail Server details as follows, also shown in below screenshot.
Specify your valid gmail Id.
Server Name will be ‘smtp.gmail.com’.
port number – 587
Check the SSL for a secure connection.
In SMTP Authentication: You have to option either select window authentication using database engine service credentials or Basic Authentication.
Here we are selecting a basic authentication, and specified valid gmail Id and passwords.
Finally click on OK button.
Once you click in Ok button, you will see account details that you created are auto filled in SMTP account section.
Then Click on Next button..
After clicking on Next button, a window appears that asks you to select public profile or private profile.
In Public Profile check the profile that you have created and click on Next button.
After clicking on Next button, Click on Finish button to complete the final step.
Now you are ready to use database mail, you can check it in quickly using object explorer.
Go to object explorer, Click on Management Folder > Right click on Database Mail > Click on Send Test E-mail
Once you click on Send Test E-mail, you will see the following option for sending e – mail.
Database Mail Profile: Select database mail profile from drop down list that you want to use for sending email, it is that you have just created.
If you have multiple profile then you can choose any one that you want to use for sending e-mail.
To : Provide mail id to which you want to send e-mail, for testing purpose you can provide your mail id.
Subject: Provide e-mail subject.
Body: Specify e-mail message that will be display in e-mail body.
Finally click on Send test e-mail button
Once you click on Send test e-mail button, you will get a email notification on mail id that you provided as shown in below screenshot.
When you open above mail notification you will see a mail with body.
Troubleshooting Database Mail
If you are not getting Database Mail after configuring it, you need to got through few more step to fix it.
First you need to review your database mail log, may be you get some hint and able to fix it by yourself.
Go to object Explorer > click on Management folder > then Right click on Database Mail > then, Click on view database mail log.
or you can also run below T-SQL Code.
SELECT * FROM msdb.dbo.sysmail_event_log;
If still you do not get any idea then check whether the database mail setting is enabled or disabled, It should be enable.
To check whether the Database Mail is enabled or disabled just run the following t-sql statement.
As you can see the output,ย for the ‘Database Mail Xps’ย the Run_value and config value column value is 1, that means database mail is enabled.
sp_configure 'show advanced', 1;
GO
RECONFIGURE;
GO
sp_configure;
GO
If it is fine, then check one more step In case if you are using Gmailย SMTP server.
Database Mail works fine using the Hotmail or Outlook SMTP server details. In case of Gmail you need to
configure some settings in Gmail account as followings.
Open your Gmail account then go to URL https://myaccount.google.com/lesssecureapps
Once it opens then do enable the option to “Allow less secure apps”. This should be enabled .
Configuration of Database Mail using T-SQL Code
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'DatabaseMailAlertProfile', @description ='This is database Mail Alert profile.'; GO
EXECUTE msdb.dbo.sysmail_add_principalprofile_spย ย
ย ย ย ย @profile_name = 'DatabaseMailAlertProfile',ย ย
ย ย ย ย @principal_name = 'public',ย ย
ย ย ย ย @is_default = 1 ;
GO
EXECUTE msdb.dbo.sysmail_add_account_spย ย ย ย ย ย @account_name = 'DatabaseMailAccount',ย ย ย ย ย ย @description = 'This is Database Mail Account',ย ย ย ย ย ย @email_address = '*******@gmail.com',ย ย ย ย ย ย @display_name = 'Database Mail Alert',ย ย ย ย ย ย @mailserver_name = 'smtp.gmail.com', ย ย ย ย @port = 587, ย ย ย ย @enable_ssl = 1, ย ย ย ย @username = '*******@gmail.com', ย ย ย ย @password = 'provide the password for gamil id' ;ย ย GO
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'DatabaseMailAlertProfile', @account_name = 'DatabaseMailAccount', @sequence_number =1 ; GO
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DatabaseMailAlertProfile', @recipients = 'provided recipients valid mail id', @body = 'provide e- mail message', @subject = 'provide e-mail subject'; GO
EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = 'DatabaseMailAlertProfile' EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp @profile_name ='DatabaseMailAlertProfile' EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = ''DatabaseMailAccount' EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = 'DatabaseMailAlertProfile'
How to send a SQL Server query results in e-mail message
Troubleshooting Database Mail in SQL Server
2,652 total views, 11 views today