Who is this article for?

Database administrators who need to identify and requeue failed emails directly in the database.

Database permissions are required.

This article explains how to identify and requeue failed emails using SQL queries.

1. Identify failed emails

The following query returns all failed email entries. Email history is retained for 30 days by default.

SELECT * FROM T_QueueEntry WHERE QueueEntryType = 'SendEmail' AND Status = 'F';

Example of running the query in SQL Server Management Studio:

Double-click the database name so it appears in the query window header, select New query, paste the SQL, and select Execute to view results.

If the emails were not marked as failed, you can filter by date range instead:

SELECT * FROM T_QueueEntry 
WHERE QueueEntryType = 'SendEmail' 
AND StartDate = '2023-07-20' 
AND StartDate <= 2023-07-24="2023-07-24">

2. Back up the table before updating

Always back up the table before making updates. The following examples create a backup table with a date suffix.

SQL Server:

SELECT * INTO Z_T_QueueEntry_Backup_20230725 FROM T_QueueEntry;

Oracle:

CREATE TABLE Z_T_QueueEntry_Backup_20230725 AS SELECT * FROM T_QueueEntry;

3. Requeue the failed emails

To requeue emails, replace the SELECT statement with an UPDATE statement.

Original:

SELECT * FROM T_QueueEntry WHERE ...

Updated:

UPDATE T_QueueEntry SET Status = 'Q' WHERE ...

Examples:

UPDATE T_QueueEntry 
SET Status = 'Q' 
WHERE QueueEntryType = 'SendEmail' 
AND Status = 'F';

UPDATE T_QueueEntry 
SET Status = 'Q' 
WHERE QueueEntryType = 'SendEmail' 
AND StartDate = '2023-07-20' 
AND StartDate <= 2023-07-24="2023-07-24">

The background service checks every minute and will attempt to resend the requeued emails. If the service is busy processing documents, it may take additional time before email processing begins.