Problem:
How to know the status, content and log of the database mail?
Solution:
In the last post as we were able
to send emails for a SQL Server Agent job history, and for alerting when a job
fails. As there is lot of activity going on the Database mail, how do we know
whether any email went through or not, or the content of the mail, recipients
and other information.
Each mail item sent from database
mail gets an entry inside system tables of database mail tables. For the ease
of the usage Microsoft has some system views created on this tables to retrieve
the data sets.
All these database mail views and
tables can help us to track the information and activity of Mail server,
account, profile and other details.
Example:
Example 1: In this example I would
like to see all the information of unsent items.
USE msdb
GO
SELECT sml.description
as mail_description,
smm.recipients,
smm.subject,
smat.attachment,
smat.filename,
smatt.transfer_id
FROM
[dbo].[sysmail_log] sml
INNER JOIN
[dbo].[sysmail_mailitems] smm
ON smm.mailitem_id=sml.mailitem_id
LEFT JOIN
[dbo].[sysmail_attachments] smat
ON smm.mailitem_id=smat.mailitem_id
LEFT JOIN [dbo].[sysmail_attachments_transfer]
smatt
ON smat.attachment=smatt.attachment
and smm.sent_status=0
OUTPUT:
mail_description
|
recipients
|
subject
|
attachment
|
filename
|
transfer_id
|
The mail could not be sent to the recipients
|
sqldevmahesh@gmail.com
|
Database Mail Test
|
NULL
|
NULL
|
NULL
|
The mail could not be sent to the recipients
|
sqldevmahesh@gmail.com
|
Database Mail Test
|
NULL
|
NULL
|
NULL
|
The mail could not be sent to the recipients
|
sqldevmahesh@gmail.com
|
Database Mail Test
|
NULL
|
NULL
|
NULL
|
The mail could not be sent to the recipients
|
sqldevmahesh@gmail.com
|
Database Mail Test
|
NULL
|
NULL
|
NULL
|
Example 2:
In this example I am going to show you to determine the server type of
an email account and whether it’s an incoming server or outgoing server.
USE msdb
GO
SELECT
sma.name
as account_name,
smp.name
as profile_name,
sms.servertype,
sms.servername,
sms.port,
CASE
WHEN smsty.is_outgoing
= 1
THEN 'OUT-GOING SERVER'
WHEN is_incoming = 1
THEN 'IN-COMING SERVER'
END [i/o]
FROM [dbo].[sysmail_account] sma
INNER JOIN [dbo].[sysmail_profileaccount]
smpa
ON sma.account_id=smpa.account_id
INNER JOIN [dbo].[sysmail_profile]
smp
ON smpa.profile_id=smp.profile_id
INNER JOIN
[dbo].[sysmail_server]
sms ON sma.account_id=smpa.account_id
INNER JOIN [dbo].[sysmail_servertype]
smsty
ON
SMs.servertype =
smsty.servertype
OUTPUT:
account_name
|
profile_name
|
servertype
|
servername
|
port
|
i/o
|
DBMail
|
DB Mail Public Profile
|
SMTP
|
0.0.0.0
|
25
|
OUT-GOING SERVER
|
Please go
through this article send
email from Database, on how to send a mail.
0 comments:
Post a Comment