Sending Email using SQL Server

Follow the below steps to configure email account and send email using SQL Server

–Enabling Database Mail
sp_configure ‘show advanced options’,1
reconfigure
go
sp_configure ‘Database Mail XPs’,1
reconfigure

–Creating a Profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = ‘SQLProfile’,
@description = ‘Mail Service for SQL Server’ ;

— 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 = ‘abc@gmail.com’,
@mailserver_name = ‘smtp.gmail.com’,
@port=587,
@enable_ssl=1,
@username=’abc’,
@password=’password’

— Adding the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ‘SQLProfile’,
@account_name = ‘SQL_Email_Account’,
@sequence_number =1

— Granting access to the profile to the DatabaseMailUserRole of MSDB
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = ‘SQLProfile’,
@principal_id = 0,
@is_default = 1 ;

–Sending Test Mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘SQLProfile’,
@recipients = ‘abc@gmail.com’,
@body = ‘Database Mail Testing…’,
@subject = ‘Databas Mail from SQL Server’;

–Verifying, check status column
select * from sysmail_allitems

 

Advertisements

#send-email