Send emails to visitors, not real time, not a list, not a recipient, already sent to via Message Center

[hetfidd]

Hi,

I'm trying to configure recurring deliveries for the following scenario using a workflow because it is not a real time event,but we are pulling people who received a transactional email from a real time message center event:

1)  Pull non-recipients/visitors that received a welcome email via message center and are stored in the event archived history logs

2)  Send an email 1 hour after they receive the welcome email with a follow up email.  There is no personalization in the email.

I can pull the people in a query and get the results back from the archived event history table, reconcile, dedupe, but I cannot get the delivery to send even if I select the people for the targeted group and put in the same criteria based on what is in the archived event history.

I have tried to both include and not include recipients in the target and the delivery goes red.  This is the error I am getting.   Happy to get on a google hangout with a fellow user and share a screen because this is hard to describe.

TESTING ONE HOUR EMAIL NON RECIPIENT (devDM12163)

The delivery has failed

XSV-350122 An error occurred and the process has been stopped. SOP-330011 Error while executing the method 'PrepareTargetImpl' of service 'nms:delivery'. WDB-200001 SQL statement 'INSERT INTO wkDlv_88472318T (, tsEvent, sTargetCode, iDeliveryId, iTargetDataId, sAddress, iFormat, dMessageWeight) (SELECT R2.sAccount, TIMESTAMP WITH TIME ZONE '2018-10-10 10:32:39.789-05', E'__MAIN__', 88472318, W0.iTargetDataId, E1.sEmail, E1.iEmailFormat, 5 FROM wkf83656961_205_1_ALL W0, NmsEventHisto E1 LEFT JOIN NmsReci

Audit buttons says:

10/10/2018 11:32:39 AM XSV-350122 An error occurred and the process has been stopped.

10/10/2018 11:32:39 AM SOP-330011 Error while executing the method 'PrepareTargetImpl' of service 'nms:delivery'.

10/10/2018 11:32:39 AM WDB-200001 SQL statement 'INSERT INTO wkDlv_88472318T (, tsEvent, sTargetCode, iDeliveryId, iTargetDataId, sAddress, iFormat, dMessageWeight) (SELECT   R2.sAccount, TIMESTAMP WITH TIME ZONE '2018-10-10 10:32:39.789-05', E'__MAIN__', 88472318, W0.iTargetDataId, E1.sEmail, E1.iEmailFormat, 5 FROM wkf83656961_205_1_ALL W0, NmsEventHisto E1 LEFT JOIN NmsRecipient R2 ON (R2.sAccount = E1.sExternalId) WHERE ((E1.biEventHistoId = W0.biId)))' could not be executed.

10/10/2018 11:32:39 AM PGS-220000 PostgreSQL error: ERROR:  syntax error at or near "," LINE 1: INSERT INTO wkDlv_88472318T (, tsEvent, sTargetCode, iDelive...                                      ^

10/10/2018 11:32:39 AM Loading typology rules (step: javascript/preTarget)...

10/10/2018 11:32:39 AM Initializing delivery process...

[Adhiyan · Accepted solution]

Hi There ,

The error in the delivery comes from a missing column in the INSERT statement:

INSERT INTO wkDlv_88472318T (<Missing Column>, tsEvent, sTargetCode, iDeliveryId, iTargetDataId, sAddress, iFormat, dMessageWeight) .

Can you please check the other deliveries which have gone out successfully and see what column is inserted before tsEvent column.

If in the other deliveries tsEvent is the first column, then the workflow which is preparing the delivery is the root of the error feeding an empty column.

Regards,

Adhiyan