Friday, September 2, 2016

Enabling and configuring Database Mail in SQL Server using T-SQL

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. 
These steps are defined step by steps as given below
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:
Create an account for use with the profile – 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: 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 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='Congrates!!! Database Mail 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.

1 comment:

  1. 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

Popular Posts