Applies to Moxie Email Versions 9.2 and 9.4
There are multiple circumstances where email in not properly processed by the Mail email subsystems, primary related to CommCore services. The following will help identify such circumstances and offer remediation. Please contact Moxie Support if you have any questions or if the emails fails to get out of that state/status even after running the workaround.
1) Inbound email fails to parse
Moxie Email analyses inbound email to identify the email content for further processing such as routing. Some emails received may not be in a format the mail system can support, usually these are not in one of the industry email standards, known as RFCs. If email is received in a format that cannot be processes, they will remain left in the Moxie main database (in the MailFailedInParse table) and cannot sent to mailboxes.
The following script should executed periodically to monitor the database table (MailFailedInParse) where emails that fail to properly parse the format.
Select COUNT(1) from MailsFailedInParse with (NOLOCK) WHERE
(
DAY(RecvTime) = DAY(GETDATE())
AND
MONTH(RecvTime) = MONTH(GETDATE())
AND
YEAR(RecvTime) = YEAR(GETDATE())
)
If an email is found, the email may be extracted from the database and attempt to view the content with a desktop application such as a email application or text processor.
2) Email fails to process due to processing errors
In some circumstances, email may not fully process and appear in a Moxie mailbox or leave the Outbox. The primary situations where these occur are when Workflow processing errors occur or there is a database failure such as the database going into read-only mode due to space or other issues.
The following scripts will enable an automated process to identify and enable recovery of these emails.
Step1: Create two separate tables for inbound and outbound MSGIDs using TableScript in the Channels main database
Step2: Create a SQL job on the Channels main database with the JobScript and schedule it for every 30 minutes
The JobScript will execute as follows:
Check the records in Mailsendqueue and Mailmessage table for email that are there for more than 30 minutes. Emails should not be in these tables for an extended period of time
If there are any records, then insert MSGIDs into tables which were created in the step 1 above
-
Update the mailsendqueue table records that are set to state =5 and mailmessage table records with status =6
SQL Scripts for Email fails to process due to processing errors
TableScript
CREATE TABLE [gomoxie_schema].[Msgid_from_Mailmessage_inbound](
[MsgID] [int] NOT NULL,
[RouteDate] [datetime] NOT NULL,
CONSTRAINT [PK_Msgid_from_Mailmessage_inbound] PRIMARY KEY CLUSTERED
(
[MsgID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [gomoxie_schema].[Msgid_from_Mailsendqueue_outbound](
[firstattempttime] [datetime]NOT NULL,
[msgid] [int] NOT NULL,
CONSTRAINT [PK_Msgid_from_Mailsendqueue_outbound] PRIMARY KEY CLUSTERED
(
[MsgID] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
FILLFACTOR = 85) ON [PRIMARY]
) ON [PRIMARY]
GO
JobScript
declare @inbound_count int
SELECT @inbound_count=count(1)
from mailmessage where DATEDIFF(mi,routedate,getdate())>30 and status=5
if @inbound_count<>0
begin
INSERT INTO Msgid_from_Mailmessage_inbound (MSGID,Routedate)
SELECT MSGID,Routedate FROM mailmessage where DATEDIFF(mi,routedate,getdate())>30 and status=5
Update MailMessage Set Status = 6 where DATEDIFF(mi,routedate,getdate())>30 and status=5
end
declare @outbound_count int
SELECT @outbound_count=count(1)
from MailSendQueue where DATEDIFF(mi,firstattempttime,getdate())>30
and state =5
if @cnt<>0
begin
INSERT INTO Msgid_from_Mailsendqueue_outbound (Msgid,firstattempttime)
SELECT Msgid,firstattempttime FROM mailsendqueue where DATEDIFF(mi,firstattempttime,getdate())>30 and status=5
Update Mailsendqueue Set State = 0 where DATEDIFF(mi,firstattempttime,getdate())>30 and status=5
end