Environment

All versions of Content Manager

Situation

To be able to search for multiple email addresses on a Location you will need to use the following Script.

Resolution

Using this SQL query:

--drop _TSTMP if exists

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[_TSTMP]') AND type in (N'U'))

DROP TABLE [dbo].[_TSTMP];


--beginning of dump into temp table


SELECT tsloceaddr.lelocuri AS Location_URI,

Count(tsloceaddr.lelocuri) AS repetita

INTO _tstmp

FROM tsloceaddr

WHERE ( lemailtype = 'SMTP' )

GROUP BY tsloceaddr.lelocuri

HAVING Count(tsloceaddr.lelocuri) > 1;


--Combine data from temp table and Location data


SELECT tslocation.lcname AS Location_Name,

tsloceaddr.lelocuri AS Location_URI,

tsloceaddr.leaddress AS EMAIL_Address,

tsloceaddr.lemailtype

FROM _tstmp

INNER JOIN tslocation

ON _tstmp.location_uri = tslocation.uri

INNER JOIN tsloceaddr

ON tslocation.uri = tsloceaddr.lelocuri

WHERE leMailType = 'SMTP'

ORDER BY Location_URI;

Access support article here