This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this Post, I am sharing all necessary scripts for configuring the Database Mail in SQL Server.
SQL Server provides a very good feature like Database Mail which we can use for sending the query result via email address.
You can configure database mail using SSMS, but here I am sharing required T-SQL scripts.
Execute the below list of T-SQL scripts and configure the Database Mail:
Enable database mail:
|
1 2 3 4 5 6 7 8 9 10 11 |
USE master; GO sp_CONFIGURE 'show advanced', 1 GO RECONFIGURE GO sp_CONFIGURE 'Database Mail XPs', 1 GO RECONFIGURE GO |
Create a db main account for Gmail:
|
1 2 3 4 5 6 7 8 |
EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'DB_Email_Account', @email_address = 'test@gmail.com', @mailserver_name = 'smtp.gmail.com', @port=587, @enable_ssl=1, @username='emailaddress', @password='password' |
Create a profile:
|
1 2 3 |
EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'SQLMailProfile', @description = 'DB Mail Service for SQL Server' |
Add account to the profile:
|
1 2 3 4 |
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'SQLMailProfile', @account_name = 'DB_Email_Account', @sequence_number =1 ; |
Grant access to the profile:
|
1 2 3 4 |
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp @profile_name = 'SQLMailProfile', @principal_id = 0, @is_default = 1 |
Test database mail:
|
1 2 3 4 5 |
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQLMailProfile', @recipients = 'xyz@abc.com', @body = 'SQL Database Mail Test', @subject = 'Databas Mail from dbrnd' |
