How to find invalid email addresses in Sage 500

1 minute read time.

Here is a tip to help you find invalid email addresses associated to customer contact records. We have run across data had been imported from other systems with incomplete or wrong email addresses, that found their way into the EmailAddr field in the contact table.  These invalid email addresses will present a problem when it comes time to use the email address to send them an invoice from Document Transmittal for example.  The EmailAddr field is a varchar(256) length field that will allow blank values.  To find the contact records with invalid email addresses, here is an example of a query you could run:

SELECT EMailAddr,b.custid,b.custname,b.companyid

FROM tciContact a

INNER JOIN tarCustomer b

ON a.CntctKey=b.PrimaryCntctKey

WHERE a.EMailAddr NOT LIKE '%_@__%.__%' and a.EMailAddr <> ''

ORDER BY Companyid,CustID

The above query could probably be written many ways. The key piece in this query is in the WHERE clause. We are using pattern matching scheme with the LIKE logical operator. We want to exclude rows that have blank email addresses because those are not necessarily invalid.  We could have used a.EMailAddr NOT LIKE '%@%'  in the WHERE clause because email addresses should have the at sign symbol.  But what if the email address had the at sign but the rest of the address was incomplete.  I opted to compare against a mask that looks like what an email address normally looks like.

Once you find the list of invalid entries, I recommend changing them through the front email of Sage 500, Maintain Customers task. Use the CustID field from the query results to look up the customer record.

Before you run the query, make a copy of the database first and run it against that copy. Depending on the size of the customer and contact records, the query could take a while to complete.   Lastly, the above WHERE clause logic can be used against other tables such as stgCustomer for example that have an EmailAddr field. 

I hope you find this query useful and that it helps you clean up your email addresses.

  • If you want to check virtually all e-mail addresses in the system, try the following query as an alternative. With extra work, you could probably return key or companyID values.  Inserting the query changed the formatting a little, but it still works.

    ------------------------------------------------------------------------------

    -- Script to find Sage 500 ERP e-mail addresses that do not conform to the

    -- common e-mail address format. It finds e-mail address fields by the Sage

    -- 500 ERP DomainID in tsmDataDictFlat.

    ------------------------------------------------------------------------------

    SET NOCOUNT ON

    IF OBJECT_ID('tempdb..#NonConformingEmailAddresses') IS NOT NULL

    BEGIN

    DROP TABLE #NonConformingEmailAddresses

    END

    CREATE TABLE #NonConformingEmailAddresses

    (

    EMailAddress VARCHAR(256)

    ,TableName VARCHAR(255)

    ,ColumnName VARCHAR(255)

    )

    DECLARE

    @TableName VARCHAR(255)

    ,@ColumnName VARCHAR(255)

    ,@SQLStatement VARCHAR(8000)

    DECLARE EmailFieldsCursor CURSOR READ_ONLY

    FOR

    SELECT

    TableName

    ,ColumnName

    FROM

    tsmDataDictFlat WITH (NOLOCK)

    WHERE

    DomainID = 'LongEmailAddr'

    ORDER BY

    TableName

    ,ColumnName

    OPEN EmailFieldsCursor

    FETCH NEXT

    FROM

    EmailFieldsCursor

    INTO

    @TableName

    ,@ColumnName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    -- Build a dynamic SQL statument for the table and field.

    SELECT

    @SQLStatement =

    N'INSERT #NonConformingEmailAddresses(EMailAddress, TableName, ColumnName)'+

    ' SELECT '

    + @ColumnName

    +' , '+''''+ @TableName +''''

    +' , '+''''+ @ColumnName +''''

    +' FROM '+ @TableName

    +' WHERE COALESCE('+ @ColumnName +', '+''''''+' ) <> '''''

    -- Adjust the pattern as tight as you wish. The one included would work with [email protected]

    +' AND '+ @ColumnName +' NOT LIKE '+'''%_@_%._%'''

     

    -- Show what was searched.

    PRINT 'Searching ' + @TableName

    -- Execute the built query.

    EXECUTE (@SQLStatement)

    FETCH NEXT

    FROM

    EmailFieldsCursor

    INTO

    @TableName

    ,@ColumnName

     

    END

    CLOSE EmailFieldsCursor

    DEALLOCATE EmailFieldsCursor

    PRINT ''

    PRINT '>>> Questionable Email Addresses <<<'

    PRINT ''

    SELECT

    LEFT(EMailAddress, 50)'EMailAddress'

    ,LEFT(TableName, 50)'TableName'

    ,LEFT(ColumnName, 50)'ColumnName'

    FROM

    #NonConformingEmailAddresses

    ORDER BY

    TableName

    ,EMailAddress

  • Note that this query is only looking at the primary contact.  I adjusted it slightly to look at any of the "Other Contacts" on the customer record as well.