In my last post (Configure Database Mail In SQL Server Database) i have explained how to configure database mail using GUI in SQL server Today i am going to explain how to configure database mail using T-SQL.
1. Create a database mail profile
--Creating a Profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'TestProfile',
@description = 'Test Mail Service for SQL Server'
2. Create database mail account
-- Create a Mail account for gmail.
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'TestAccount',
@email_address = 'Your Email',
@display_name='Fresh Code Hub',
@mailserver_name = 'smtp.gmail.com',
@port=587,
@enable_ssl=1,
@username='Your Email',
@password='Your Password'
3. Add database mail account to database profile
-- Add account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'TestProfile',
@account_name = 'TestAccount',
@sequence_number =1
4. Provide access to the profile
-- Granting access to the profile
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'TestProfile',
@principal_id = 0,
@is_default = 1
5. Send test Mail
--Sending Test Mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'TestProfile',
@recipients = 'To Email Here',
@copy_recipients ='CC Email Here', --For CC Email if exists
@blind_copy_recipients= 'BCC Email Here', --For BCC Email if exists
@subject = 'Mail Subject Here',
@body = 'Mail Body Here',
@body_format='HTML',
@importance ='HIGH',
@file_attachments='C:\Test.pdf'; --For Attachments if exists