The process of SQL Server Database Mail configuration has three main steps i.e.
- Create a Database Mail account
- Create a Database Mail profile
- Configure those two to work together
How to send Email from Trigger in SQL Server?
Enabling Database Mail
USE MASTER
GO
SP_CONFIGURE 'show
advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
SP_CONFIGURE 'Database
Mail XPs', 1
RECONFIGURE WITH OVERRIDE
GO
SP_CONFIGURE 'show
advanced options', 0
RECONFIGURE WITH OVERRIDE
GO
Mail Account Setup
EXEC
msdb.dbo.sysmail_add_account_sp
@account_name = 'Test'
,@description
= 'Send emails using SQL Server Stored Procedure'
,@email_address
= 'anil.singh581@gmail.com'
,@display_name
= 'Test'
,@replyto_address
= NULL
,@mailserver_name
= 'smtp.gmail.com'
,@username
= 'anil.singh@gmail.com'
,@password
= 'YourPassword#12345'
,@port
= 587
,@enable_ssl
= 1
GO
EXEC
msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Test'
,@description
= 'Send emails using SQL Server Stored Procedure'
GO
EXEC
msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Test'
,@account_name
= 'Test'
,@sequence_number
= 1
GO
Send the Email
EXEC
msdb.dbo.sp_send_dbmail
@profile_name = 'Test'
,@recipients
= 'anil.singh581@gmail.com'
,@subject
= 'Email from SQL Server'
,@body
= 'This is my First Email sent from SQL Server :)'
,@importance
='HIGH'
GO
Delete Account Name from DB mail
/* EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = 'Test' ; */
See the Status of Email
SELECT * FROM
msdb.dbo.sysmail_unsentitems
SELECT * FROM
msdb.dbo.sysmail_sentitems
SELECT * FROM
msdb.dbo.sysmail_faileditems
SELECT
mailitem_id
,[subject]
,[last_mod_date]
,(SELECT TOP
1 [description]
FROM
msdb.dbo.sysmail_event_log
WHERE
mailitem_id = logs.mailitem_id
ORDER BY
log_date DESC) [description]
FROM
msdb.dbo.sysmail_faileditems logs
These logs have all useful information that could help in troubleshooting, and we are in particular interested in the ‘description’ column as it holds detailed information about the error and what went wrong.
|
Anil Singh is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..
|
