Article

Create a Database Mail Configuration Using T-SQL Script

05 Feb 2018 Kamal Pratap
0 Comments 940 Views



It is the enterprise solutions to send email to your customer or vendor from SQL server. It provides features like scalability, security, and reliability.

 

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

 

Kamal Pratap

I have 6+ years experience in .Net technologies like Asp.Net, C#, WCF, Web Services, SQL Server, Ajax, LinQ. Currently I am working in Netcarrots Loyalty Services as a Software Developer.

Comments

No coments found to display!

Leave a Comment