Product: DSP / ISA / DSMA
Version: all 7.x and 6.x versions
Issue: Collect Workflow Failure service page (or others) reports error message "Improperly formatted email address”.
This error means that there are one or more records in the dgSendWorkFlowFailure table that have invalid email addresses in either the EmailFrom or EmailTo fields.
These problematic records need to be manually deleted through SQL Server Management Studio. Note that even a single invalid dgSendWorkFlowFailure record will stop all valid emails from being sent.
Removing the erroneous record(s) will solve the immediate problem, but to ensure that these errors don't reoccur, the source of the erroneous record(s) must be fixed as well.
Resolution:
- Identify and remove invalid records from the dgSendWorkFlowFailure table
- Correct the source of invalid EmailFrom values
- Correct the source of invalid EmailTo values
1) Identify and remove invalid records from the dgSendWorkFlowFailure table
1.1) Use this query to identify invalid records:
SELECT EmailTo, EmailFrom, *
FROM DataGarage.dbo.dgSendWorkFlowFailure
WHERE
PATINDEX ('%[ &'',":;!+=\/()<>]%', EmailTo) > 0
or PATINDEX ('[@.-_]%', EmailTo) > 0
or PATINDEX ('%[@.-_]', EmailTo) > 0
or EmailTo NOT LIKE '%@%.%'
or EmailTo LIKE '%..%'
or EmailTo LIKE '%@%@%'
or EmailTo LIKE '%.@%' OR EmailTo LIKE '%@.%'
or EmailTo LIKE '%.cm' OR EmailTo LIKE '%.co'
or EmailTo LIKE '%.or' OR EmailTo LIKE '%.ne'
or EmailTo is NULL
or PATINDEX ('%[ &'',":;!+=\/()<>]%', EmailFrom) > 0
or PATINDEX ('[@.-_]%', EmailFrom) > 0
or PATINDEX ('%[@.-_]', EmailFrom) > 0
or EmailFrom NOT LIKE '%@%.%'
or EmailFrom LIKE '%..%'
or EmailFrom LIKE '%@%@%'
or EmailFrom LIKE '%.@%' OR EmailFrom LIKE '%@.%'
or EmailFrom LIKE '%.cm' OR EmailFrom LIKE '%.co'
or EmailFrom LIKE '%.or' OR EmailFrom LIKE '%.ne'
or EmailFrom is NULL
1.2) Use this query to make a backup table:
SELECT * INTO DataGarage.dbo.dgSendWorkFlowFailure_bkp
FROM DataGarage.dbo.dgSendWorkFlowFailure
NOTE: If the DataGarage.dbo.dgSendWorkFlowFailure_bkp already exists, then try adding a date to the table name. For example, use DataGarage.dbo.dgSendWorkFlowFailure_bkp_20191104 instead.
1.3) Finally, use this query to remove invalid records:
DELETE
FROM DataGarage.dbo.dgSendWorkFlowFailure
WHERE
PATINDEX ('%[ &'',":;!+=\/()<>]%', EmailTo) > 0
or PATINDEX ('[@.-_]%', EmailTo) > 0
or PATINDEX ('%[@.-_]', EmailTo) > 0
or EmailTo NOT LIKE '%@%.%'
or EmailTo LIKE '%..%'
or EmailTo LIKE '%@%@%'
or EmailTo LIKE '%.@%' OR EmailTo LIKE '%@.%'
or EmailTo LIKE '%.cm' OR EmailTo LIKE '%.co'
or EmailTo LIKE '%.or' OR EmailTo LIKE '%.ne'
or EmailTo is NULL
or PATINDEX ('%[ &'',":;!+=\/()<>]%', EmailFrom) > 0
or PATINDEX ('[@.-_]%', EmailFrom) > 0
or PATINDEX ('%[@.-_]', EmailFrom) > 0
or EmailFrom NOT LIKE '%@%.%'
or EmailFrom LIKE '%..%'
or EmailFrom LIKE '%@%@%'
or EmailFrom LIKE '%.@%' OR EmailFrom LIKE '%@.%'
or EmailFrom LIKE '%.cm' OR EmailFrom LIKE '%.co'
or EmailFrom LIKE '%.or' OR EmailFrom LIKE '%.ne'
or EmailFrom is NULL
2) Correct the source of invalid EmailFrom values
- Go to Common/Configuration/Modules/Parameters/Collect - Workflow Settings tab.
- Edit that page and update the Collect Work Flow From Email field to a valid email address.
3) Correct the source of invalid EmailTo values
- Go to Admin/Security/WebApp Security.
- Click on the Groups icon next to Collect.
- Click on the Users icon next to the WorkFlowFailureAll Group.
- Note all users under that group.
- Repeat steps 3 & 4 for the WorkFlowFailureByTargetAccess group.
- Go into SQL Server Management Studio and run this query:
SELECT UserID, Name, EmailAddress from CranSoft.dbo.[User] - Review the results of the query and identify any users that in the WorkFlowFailureAll and/or WorkFlowFailureByTargetAccess groups who do have a missing or invalid email address associated with them in the User table.
- Go to Admin/Security/Users.
- Go to the vertical view of the user(s) identified in step 7 and update their EmailAddress to a valid value.