Error Initializing Cross Reference Cache in Application Modules Diagnostic View

Issue
Unable to edit sources or measurements on existing gadget or create new gadget. The application modules diagnostic view will include the following message:
Error initializing cross reference cache. ---> Framework.Common.Exceptions.ModuleException: Data Source Device [XXXXX] is defined multiple times. where XXXXX is the name of a device in the system.

Product
Power Monitoring Expert (PME) 8.x, 9.0, 2020

Environment
SQL Server Management Studio

Cause
Multiple entries in the [ApplicationModules].[DataSourceBus].[DataSourceDevice] and/or [ApplicationModules].[DataSourceBus].[LogicalDevice]  tables

Resolution
*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*
A) Determine the LogicalDeviceId in the DataSourceDevice table
1) Use Applicationmodule
Select Description, count(*) from [DataSourceBus].[DataSourceDevice] group by [description]
having count(*) > 1

2) Use the description field in the query below;
Use Applicationmodules
Select Description, LogicalDeviceID, from [DataSourceBus].[DataSourceDevice] where [description] like 'xxxxxxx'

B) Remove the duplicate entries
1) Make backup of AppicaltionModuless database
2) Shut down the AppMods services
3) Run ApplicationModules-DeleteAllDeviceMetadata.sql attached (the device metadata will get rebuilt)
4) Run the following query where xxx,yyy,zzz are LogicalDeviceIds found in section A
-- delete duplicate devices
delete from DataSourceBus.datasourcedevice where DatasourcedeviceID in (xxx,yyy,zzz)
delete from DataSourceBus.logicaldevice where logicaldeviceID in (xxx,yyy,zzz)
5) Start the AppMods services
6) Check the Diagnostics.LogEvent table to verify that Data Source Bus initialized correctly

Released for:Schneider Electric Canada