ACS Email Proofs not sending - Substitution ID | Community
Skip to main content
T
tommy_8
Level 2
August 27, 2024
Solved

ACS Email Proofs not sending - Substitution ID

  • August 27, 2024
  • 2 replies
  • 865 views

hey ACS users,

 

we're getting an error when trying to send proofs for a specific delivery...

 

Error messages:

WDB-200001 SQL statement 'INSERT INTO wkf123274640_1_1 (biMessageId,sAddress,iFormat,sVariant,sTargetCode,iStatus,iBlackList,iProfileId,iProfileId1,sProfileSubstitutionPrefix,iProfileGender,sProfileFirstName,sProfileLastName,sCusLinkProfileAgencyName01,sCusLinkProfileAgencyName02,sCusLinkProfileAgencyStreet,sCusLinkProfileAgencyZipCode,sCusLinkProfileAgencyCity,sCusLinkProfileAgencyEmail,sPhoneNumber,tsEvent,iDeliveryId,tsExpiration) SELECT W0.biMessageId, W0.sAddress, W0.iFormat, W0.sVariant, W0.sTargetCode, W0.iStatus, W0.iBlackList, W0.iProfileId, N1.iRecipientId, P2.sPrefix, N1.iGender, N1.sFirstName, N1.sLastName, C5.sName01, C5.sName02, C5.sStreet, C5.sZipCode, C5.sCity, C5.sEmail, C5.sPhoneNumber, W0.tsEvent, 123270913, NULL FROM wkDlv_123270913T W0 LEFT JOIN NmsRecipient N1 ON (N1.iRecipientId = W0.iProfileId) LEFT JOIN NmsProfileSubstitution P2 ON (P2.iProfileSubstitutionId = W0.iProfileSubstitutionId) JOIN wkf122737358_query2 W3 ON (W3.iId = W0.iProfileId) JOIN NmsRecipient N4 ON (N4.iRecipientId = W3.iId) LEFT JOIN CusDto_agency C5 ON (C5.AgencyNo = N4.CusDtoLastAgency) WHERE (W0.iFailureReason = 0) AND (W0.iFcp <> 0 OR W0.iInboxRendering <> 0)' could not be executed.

 

PGS-220000 PostgreSQL error: ERROR: relation "wkf122737358_query2" does not exist LINE 1: ...eSubstitutionId = W0.iProfileSubstitutionId) JOIN wkf1227373... ^

 

seems like the profile substitution isn't working.

however, we don't use that option in the proof settings:

 

 

anybody experienced a similar behaviour?

 

thanks in advance, best

Thomas

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by ccg1706

Hi @tommy_8 ,

 

The SQL error occurs because the query is trying to insert data into a temporary table while joining other tables, but one table ( "wkf122737358_query2") does not exist or is incorrectly referenced. Despite profile substitution is not enabled, the query still attempts to join with the ("NmsProfileSubstitution") table, showing possible residual settings or misconfigurations.

 

To solve the error, review the workflow and ensure al queries and temporary tables are correctly set up and referenced. Double-check profile substitution settings, even if not enabled, and examine target mapping for issues with the profile selection. Clear cache and restart the workflow to remove any issues. Finally, take a look to the database logs for any underlying issues that might affect query execution.

 

Hope this helps!

 

Regards, 

Celia

 

 

2 replies

ccg1706
Community Advisor
ccg1706Community AdvisorAccepted solution
Community Advisor
August 28, 2024

Hi @tommy_8 ,

 

The SQL error occurs because the query is trying to insert data into a temporary table while joining other tables, but one table ( "wkf122737358_query2") does not exist or is incorrectly referenced. Despite profile substitution is not enabled, the query still attempts to join with the ("NmsProfileSubstitution") table, showing possible residual settings or misconfigurations.

 

To solve the error, review the workflow and ensure al queries and temporary tables are correctly set up and referenced. Double-check profile substitution settings, even if not enabled, and examine target mapping for issues with the profile selection. Clear cache and restart the workflow to remove any issues. Finally, take a look to the database logs for any underlying issues that might affect query execution.

 

Hope this helps!

 

Regards, 

Celia

 

 

    Sukrity_Wadhwa
    Community Manager
    Sukrity_WadhwaCommunity Manager
    Community Manager
    September 12, 2024

    Hi @tommy_8,

    Was the given solution by @ccg1706 helpful to resolve your query or do you still need more help here? Do let us know.

    Thanks!

    Sukrity Wadhwa
      T
      tommy_8Author
      Level 2
      September 13, 2024

      hi @sukrity_wadhwa @ccg1706 

      we are still in bug fixing mode.

      However the hint was quite helpful in order to take some steps back. It's obviously not a problem of the delivery.

      Thanks, Thomas

        Terms & ConditionsAccessibility statement

        Loading footer...