Problem:
How to configure Database Mail.?
Solution:
If we need to send alerts, notifications, Job status of a SQL Server
agent from SQL Server Database. You may have to configure Database Mail to send
e-mail message to concerned persons as it keeps notifying when there is a fatal
error or failure of a SQL job. So, it keeps you aware of the process even
though you are not on the computer.
You can configure query results
into an attachments and also in the body, also configure subject of email, etc.
To do this we have few things to
do.
·
Enable Database Mail
Example:
In this article I am going to show you we can create a mail account,
profile and add the account to profile using T-SQL, also view the results after
configuring.
Step 1: enabling the database using sp_configure to set the server
configurations.
EXEC sp_configure 'show advanced options', '1';
RECONFIGURE
GO
EXEC sp_configure 'Database Mail XPs', 1;
RECONFIGURE
GO
SELECT *
FROM sys.configurations where
name='Database Mail
XPs';
GO
Step 2:
Creating a database mail account, passing parameters to the system stored
procedure to configure from which the mail needs to be send and also the server
from where it sends the email from.
--
Create a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name
= 'DBMail',
@description =
'Mail account for use by all database users.',
@email_address =
'learnersSQL@gmail.com',
@replyto_address =
'LearnersSQL@gmail.com',
@display_name =
'Database Automated Mailer',
@mailserver_name =
'smtp.gmail.com,
@port=587;
select * from
dbo.sysmail_account
OUTPUT:
account_id
|
name
|
description
|
email_address
|
display_name
|
replyto_address
|
3
|
DBMail
|
Mail account for use by all database users.
|
learnersSQL@gmail.com
|
Database Automated Mailer
|
LearnersSQL@gmail.com
|
Step 3:
Creating a mail profile to hold the mail account and for sending the mail we
need a profile.
--
Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name
= 'DB Mail Public
Profile',
@description =
'Profile used for DB Alerts mail.' ;
select * from dbo.sysmail_profile
OUTPUT:
profile_id
|
name
|
description
|
last_mod_datetime
|
3
|
DB Mail Public Profile
|
Profile used for DB Alerts mail.
|
2/24/2017 16:37
|
Step 4: adding the account to the profile. Both the profile and the
account must already exist. Otherwise, the stored procedure returns an error.
--
Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name
= 'DB Mail Public Profile',
@account_name =
'DBMail',
@sequence_number =1
;
select * from
dbo.sysmail_profileaccount
OUTPUT:
profile_id
|
account_id
|
sequence_number
|
last_mod_datetime
|
3
|
3
|
1
|
2/24/2017 16:37
|
In the next article let’s explore on how to send an email from
DB engine using the profile.
0 comments:
Post a Comment