Skip to content

SqlSkull

SQL Server | SSRS | SSIS | Power BI | DAX
  • Home
  • About Us
  • Microsoft SQL Server
    • SQL Basics Concepts
    • SQL Advance Concepts
    • SQL Interview Q & A
    • SQL Server Tricks & Tips
  • SSRS
  • SSIS
  • POWER BI
  • Python with ML
  • Microsoft Azure
  • HTML Color picker

SqlSkull

SQL Server | SSRS | SSIS | Power BI | DAX
  • Home
  • About Us
  • Microsoft SQL Server
    • SQL Basics Concepts
    • SQL Advance Concepts
    • SQL Interview Q & A
    • SQL Server Tricks & Tips
  • SSRS
  • SSIS
  • POWER BI
  • Python with ML
  • Microsoft Azure
  • HTML Color picker
Home » SQL Server Database Mail Configurations

SQL Server Database Mail Configurations

  • by Pradeep Raturi
  • SQL Advance, SQL SERVER

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.

  1. Creating a Database Mail account.
  2. Creating a Database Mail profile.
  3. 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

First you need to enable the database Mail, run the following T-SQL code.
sp_configure‘show advanced options’, 1;
GO
RECONFIGURE;
GO

sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
Now create a Database profile using sysmail_add_profile_sp stored procedure as following.
EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = 'DatabaseMailAlertProfile',

@description ='This is database Mail Alert profile.';

GO
Now grant a permission for a database user or role to use this Database Mail profile, use the sysmail_add_principalprofile_sp stored procedure as following.
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp  
    @profile_name = 'DatabaseMailAlertProfile',  
    @principal_name = 'public',  
    @is_default = 1 ;
GO
After that create a new Database mail account, use the sysmail_add_account_sp stored procedure as following.
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
Now add the Database Mail account to the Database Mail profile, use the sysmail_add_profileaccount_sp  stored procedure as following.
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = 'DatabaseMailAlertProfile',

@account_name = 'DatabaseMailAccount',

@sequence_number =1 ;

GO
After linking the Database Mail Account with Database Mail Profile, you can test the Database Mail Configuration, using the msdb.dbo.sp_send_dbmail stored procedure as following.
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
If you get any error after configuring e-mail or unable to send e-mail, you can also delete the configuration of database mail account and database mail profile from SQL Server using following T-SQL Code.
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

SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More

 

 4,475 total views,  8 views today

Share this:

  • Click to share on Facebook (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on WhatsApp (Opens in new window)
  • Click to share on Reddit (Opens in new window)

Related

Tags:configure database mail in sqlCreate database mail account in sqldatabase mail account create in sqldatabase mail configuration in sql serverdatabase mail profilemsdb.dbo.sp_send_dbmailmsdb.dbo.sysmail_add_account_spmsdb.dbo.sysmail_add_profile_spsend mail in sqlsetup database mail in sqlsetup sql mailsp_send_dbmailSQLsql database mailsql database mail account and profile setupsql mail account configurationsql mail account setupsql mail alertSQL Serversql server database mailsql server database mail profileSQL Server TutorialsSQL Server Tutorials By Pradeep Raturisql setup mailSQL Tutorials

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

previousDisplaying Emoji in SQL Server
nextTroubleshooting Database Mail in SQL Server
Generic selectors
Exact matches only
Search in title
Search in content

SQL Advance

  • SQL Advance
    • SQL Server Stored Procedure
    • How to Encrypt a Stored Procedure in SQL Server
    • Insert the output of a stored procedure into table/temp table
    • SQL Server Stored Procedures Vs User Defined Functions
    • SQL SERVER VIEW
    • SQL Server OUTPUT CLAUSE
    • SQL MERGE
    • SQL Server PIVOT
    • SQL Server Dynamic PIVOT
    • SQL Server UNPIVOT
    • SQL SERVER ROLLUP
    • SQL Server Synonyms
    • SQL Server CUBE
    • SQL Server GROUPING SETS
    • SQL SERVER User Defined Table Type and Table Valued Parameters
    • USER DEFINED FUNCTIONS
    • SQL Server Create SCHEMA
    • SQL Server Sequence
    • SQL Server OFFSET FETCH
    • SQL Server CTE
    • SQL Server Recursive CTE
    • SQL Server Change Data Capture
    • SQL Server TEMPORAL TABLE
    • SQL Server Cursor
    • SQL WAITFOR
    • IDENT_CURRENT Function
    • DROP IF EXISTS
    • SQL ERROR HANDLING WITH TRY..CATCH
    • SQL SERVER IF..ELSE
    • SQL GOTO Statement
    • SQL Server WHILE
    • SQL Return
    • SQL JSON_VALUE Function
    • SQL Server FOR JSON Clause
    • SQL JSON_QUERY Function
    • SQL JSON_MODIFY Function
    • SQL Server FOR XML Clause
    • SQL SERVER SET XACT_ABORT
    • SET STATISTICS IO
    • SQL SET Language
    • SQL SET ROWCOUNT
    • SQL SET NOCOUNT ON
    • SQL SERVER SET IDENTITY_INSERT
    • SQL SERVER SET DATEFORMAT
    • SQL CLUSTERED INDEX
    • Difference between clustered index and non clustered index
    • SQL Triggers
      • SQL DML AFTER TRIGGER
      • SQL DML Instead Of Trigger
      • SQL LOGON Trigger
      • SQL DDL Trigger
    • Get the definitions of Stored procedures, User Defined functions and Triggers in SQL
    • SQL ROLLBACK Transactions
    • SQL Server Dirty Reads in concurrent transactions
    • Lost update problem in concurrent transations
    • Identify Blocked Processes or Long Running Query In SQL Server
    • Schedule a Job in SQL
    • SQL sp_helptext
    • SQL Server sp_procoption startup procedures
    • SQL Server sp_executesql
    • SQL SERVER SP_MONITOR
    • SP_RENAME in SQL Server
    • SQL sp_help
    • How to Read SQL Server error logs using the xp_readerrorlog
    • SQL Server Database Mail Configurations
    • Send a SQL Server query results in e-mail message
    • Troubleshooting Database Mail in SQL Server
    • Import file in SQL server
    • Export data from SQL Server to an Excel file
    • Bulk Insert in SQL Server
    • Copy or delete files from directory in SQL Server
    • Row Level Security in SQL Server
    • Dynamic Data Masking in SQL Server
    • SQL Vulnerability Assessment
    • Column Level Encryption Decryption using Symmetric Keys
    • SQL Data Discovery and Classification
    • Script table in SQL Server
    • Generate Database Scripts With Data in SQL Server
  • SQL Basics Concepts
  • SQL Interview Q & A
  • SQL Server Tricks & Tips
  • SSRS-SQL Server Reporting Services

Page Views

  • 651,472 hits

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Translate this website..

Search for Tutorial

Generic selectors
Exact matches only
Search in title
Search in content
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy
  • Home
  • SQL Basics Concepts
  • SQL Advance Concepts
  • SQL Interview Q & A
  • SQL Server Tricks & Tips
  • SSRS-SQL Server Reporting Services
  • SSIS-SQL Server Integration Services
  • POWER BI
  • Python with Machine Learning
  • Microsoft Azure
  • HTML Color picker

SqlSkull. All Rights Reserved

 

Loading Comments...