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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s