Configuring Database Mail Using T-SQL Script

Configuring Database Mail Using T-SQL Script

--Enabling Database Mail

sp_configure 'show advanced options',1
reconfigure
go
sp_configure 'Database Mail XPs',1
reconfigure
go

--Creating a Profile

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'SQLMailProfile',
@description = 'Mail Service for SQL Server' ;
go

-- Create a Mail account for gmail. We have to use our company mail account.

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'SQL_Email_Account',
@email_address = 'computertrik@gmail.com',
@mailserver_name = 'smtp.gmail.com',
@port=587,
@enable_ssl=1,
@username='computertrik@gmail.com',
@password='Emailid password'
go

-- Adding the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'SQLMailProfile',
@account_name = 'SQL_Email_Account',
@sequence_number =1 ;
go

-- Granting access to the profile to the DatabaseMailUserRole of MSDB

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'SQLMailProfile',
@principal_id = 0,
@is_default = 1 ;
go

--Sending Test Mail

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLMailProfile',
@recipients = 'youremailid@xxxx.com',
@body = 'Database Mail Testing...',
@subject = 'Databas Mail from SQL Server';
go

--Verifying, check status column

select * from sysmail_allitems
go

Summary 
Database Mail feature was introduced in SQL Server 2005 version, which can be used to notify the administrators/operators. It provides better performance as well as cluster aware feature.