734
To retrieve records of sent emails from your SQL Server, use T-SQL to query the “sysmail_allitems” or the “sysmail_sentitems” table in the “msdb” database. To filter the results to only include sent emails, add the condition “WHERE sent_status = ‘sent'”. This query will provide you with the date and time the email was sent, its recipients, subject, and body. Give it a try and streamline your email tracking process today! And if you found this helpful, feel free to share it with your colleagues and friends in the industry. Happy emailing!
Script that uses the “sysmail_allitems“
T-Sql
---------------------------------------------------------------------------------
-- Retrieve records of all sent emails from the SQL Server Database Mail feature
---------------------------------------------------------------------------------
SELECT
sent_status, -- The status of the email (sent, unsent, retrying, failed)
sent_date, -- The date and time the email was sent
recipients, -- The email addresses of the recipients
[subject], -- The subject of the email
body -- The content of the email
FROM
msdb.dbo.sysmail_allitems -- The system table used to keep track of all email messages sent using Database Mail
WHERE
sent_status = 'sent' -- Filter the results to only include sent emails
Script that uses the “sysmail_sentitems“
T-Sql
---------------------------------------------------------------------------------
-- Retrieve records of all sent emails from the SQL Server Database Mail feature
---------------------------------------------------------------------------------
SELECT
sent_status, -- The status of the email (sent, unsent, retrying, failed)
sent_date, -- The date and time the email was sent
recipients, -- The email addresses of the recipients
[subject], -- The subject of the email
body -- The content of the email
FROM
msdb.dbo.sysmail_sentitems -- The system table used to keep track of all email messages sent using Database Mail
WHERE
sent_status = 'sent'; -- Filter the results to only include sent emails
