Database mail is the most useful feature of SQL Server which allows us to send email though the database. This feature can be used in reporting services, integration services and in our application also.
By using this feature, we can easily tracks the email logs also and schedule these email alerts by the help of SQL Server Agent.
Well, we will learn how to enable and
configure database mail in SQL Server using T-SQL codes without using Database
Mail Configuration Wizard.
So, we are dividing our T-SQL code into six steps
which are executed in SQL one by one.
The below steps will be defined step by steps as given below–
🔍Enable the Database Mail feature🔍Create a profile for Database Mail🔍Define the default profile🔍Add an Email account with SMTP Server🔍Add the Account to the Profile🔍Send a test email to User’s email Address
Step
1: Enable the Database Mail feature- We
need to enable the Database Mail XPs parameter through the sp_configure stored
procedure, as given below:
USE [master]
GO
-- STEP 1: Enable
Database Mail Extended Procedures – TSQL Option
sp_configure 'show advanced
options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Database Mail XPs',1
GO
RECONFIGURE
GO
|
Step
2: Create a profile for Database Mail-
We need to create an email profile before creating an account. This can be done
with the help of existing stored procedure called sysmail_add_profile_sp in msdb database as given below:
-- STEP 2: Create
a New Mail Profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DBANotifications',
@description = 'Profile for sending
Automated DBA Notifications'
GO
|
Step
3: Define the default profile –
We have created the email profile in the system database. Now, we need to set
this profile as the default as given below:
-- STEP 3: Set
Profile as the default for all sql emails
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'DBANotifications',
@principal_name = 'public',
@is_default = 1 ;
GO
|
Step
4: Add an Email account with SMTP Server– After creating the email profile, we need to create an
account. As you know that this is the most important part of database mail
configuration and it should require an authorized email id and smtp server with
port number as given below:
-- STEP 4: Create
An Account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'SQLNotifications',
@description = 'Account for
Automated DBA Notifications',
@email_address = 'email@domain.com', -- Replace with your email is
@display_name = 'SQL Monitor',
@mailserver_name = 'smtp.domain.com' -- Replace with your email smtp server
GO
|
Step
5: Add the account to the profile –
Now we have created profile and account for the database mail. We could have multiple
profile and accounts in the database. So, we need to add the account to the
email profiler as given below:
-- STEP 5: Add the Account to the Profile EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'DBANotifications', @account_name = 'SQLNotifications', @sequence_number = 1 GO |
Step
6: Send a test email to the DBAs email address – After
adding account to profiler, we can send a test email as given below:
-- STEP 6: Send
Email by the profiler
EXEC msdb.dbo.sp_send_dbmail
@profile_name='DBANotifications',
@recipients='abc@your.com',
@subject='Test message',
@body='Congrates!!!
Database Mail Received By you Successfully.'
|
T-SQL
Code in a glance:
USE [master]
GO
-- STEP 1: Enable
Database Mail Extended Procedures – TSQL Option
sp_configure 'show advanced
options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Database Mail XPs',1
GO
RECONFIGURE
GO
-- STEP 2: Create
a New Mail Profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DBANotifications',
@description = 'Profile for sending
Automated DBA Notifications'
GO
-- STEP 3: Set
Profile as the default for all sql emails
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'DBANotifications',
@principal_name = 'public',
@is_default = 1 ;
GO
-- STEP 4: Create
An Account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'SQLNotifications',
@description = 'Account for
Automated DBA Notifications',
@email_address = 'email@domain.com', -- Replace with your email is
@display_name = 'SQL Monitor',
@mailserver_name = 'smtp.domain.com' -- Replace with your email smtp server
GO
-- STEP 5: Add the
Account to the Profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'DBANotifications',
@account_name = 'SQLNotifications',
@sequence_number = 1
GO
-- STEP 6: Send
Email by the profiler
EXEC msdb.dbo.sp_send_dbmail
@profile_name='DBANotifications',
@recipients='abc@your.com',
@subject='Test message',
@body='Congrats!!! database email is received by you successfully.'
|
Conclusion
To enable your database email through
T-SQL codes, you should have the sysadmin rights. This code is very useful especially
when you’re setting up Database Mail for multiple instances and multiple SMTP
servers. You can create store procedure to send various emails from multiple
SQL instances which makes your life easier to automate these email alerts.
⭐To learn more, please follow us -⭐To Learn more, please visit our YouTube channel at -⭐To Learn more, please visit our Instagram account at -⭐To Learn more, please visit our twitter account at -⭐To Learn more, please visit our Medium account at -
Hi Mukesh, I tried the same as stated but didn't work. After configuring this, manually, it worked: "Sql Server Agent -> Properties -> Alert System -> Mail Session -> Enable Mail Session -> Check It (if not already) and Confirm mentioned Mail Profile." Thanks for this Article
ReplyDelete