A meter was installed into the StruxureWare Power Monitoring (SPM) or Power Monitoring Expert (PME) system and has been logging data, waveforms, and events for any length of time. For whatever the reason, this meter's data is no longer required and it is desired that all traces of this meter be completely removed from the databases.
StruxureWare Power Monitoring 7.0.x (SPM)
Power Monitoring Expert 7.2.x (PME)
Power Monitoring Expert 8.x (PME)
Power Monitoring Expert 9.0 (PME)
Power Monitoring Expert 2020 (PME)
SQL Server Management Studio
A meter was added to the software but is no longer needed. Some reasons may include: data is no longer needed; spelling error; test meter data deletion; etc.
*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.*
- Back up the following databases: ION_Network, ION_Data, ApplicationModules.
- Stop the ION Log Inserter service. It will also be necessary to stop the Windows services related to the Application Modules features. In SPM 7.0.x, these services start with the name "Schneider Electric"; in PME 7.2.x and PME 8, these services were re-branded and start with "ApplicationModules".
- Open the Management Console and locate the device in the list. Take note of the full group.name of the device as this information will be needed later to configure a SQL script. NOTE: It is entirely possible the device is not in the Management Console. If this is the case, it is likely the full group.name is already known.
- Delete the device from the Management Console. If the following prompt appears, select Cancel and then proceed to Step 5:
4a) If PME 8.0, be sure to delete this source from any configured sw alarms, Comms Loss or otherwise. If you do not do this, the Alarming system will recreate the source.
- If there are logical devices associated with the device to be deleted, the logical devices must first be removed. Take note of each group.name of the logical devices as these will be needed later to configure a SQL script. Once each of the logical devices has been deleted from the Management Console, repeat Step 4 with the original device.
- Once all devices have been documented by group.name and deleted from the Management Console, the following SQL scripts must be configured to delete each device individually.
For SPM 7.0.x
and PME 7.2.x
, use SQL Script "SPM7_delete_device_v3.sql
" for devices which use the standard "Group.Device" naming convention. For virtual devices which do NOT
contain a period "." to separate the group and device name use SQL Script "SPM7_delete_virtual_device_v3.sql
For PME 8
, use SQL Script "PME8_delete_device_v3.sql
" for devices which use the standard "Group.Device" naming convention. For PME 8
, virtual devices which do NOT
contain a period "." to separate the group and device name use SQL Script "PME8_delete_virtual_device_v3.sql
For PME 9
, use SQL Script "Delete Device PME90 V5.sql
". For PME 9
, virtual devices which do NOT
contain a period "." to separate the group and device name use SQL Script "PME9_delete_virtual_deviceV5.sql
6a) Toward the top of the attached script, there is a code segment that looks like this:
for devices using the "SPM7_delete_device_v3.sql" or "PME8_delete_device_v3.sql" scripts
-- will need to put in group and name for device to be deleted - in this example device name is 'test.8600'
SET @oldGroupName = 'test'
SET @oldDeviceName = '8600'
for virtual devices using the "SPM7_delete_virtual_device_v3.sql" or "PME8_delete_virtual_device_v3.sql" or "PME9_delete_virtual_deviceV5.sql" scripts
-- will need to put in name for virtual device to be deleted - in this example the virtual device name is 'test'
SET @VirtualMeterName = 'test'
6b) Replace 'test' with the group
documented earlier for physical devices or the name
of the virtual device. Be sure to leave the single quote characters.
6c) Replace '8600' with the name
documented earlier. Be sure to leave the single quote characters.
The scripts below reference the "Name" Column from the ION Databases (not the DisplayName column), if the devices to be deleted have been renamed, you will need to reference the "Name" Column in the ION_Network dbo.SRC_Source table to get the proper device Name to enter to the script.
- Save and then execute the SQL script. NOTE: There may be additional cleanup required if the VIP was used to log data from the deleted device. Links to the VIP modules that reference the deleted device will have to be removed manually using Designer. If this is not done, the System Log in the Management Console will being throwing errors.
- Restart the four services stopped at the beginning of this procedure.
To do this, expand the ION_Network table and right click on the dbo.SRC_Source table and select top 1000 rows.
Navigate to your device by scrolling, or filtering for the DisplayName as shown in the image below.
Kindly see the image below as a reference:
You will need to enter the device name as the string shown under the "Name" column: Group name as"Test" and the Device Name as "Old" in order for the script to work correctly.
Note: It may be necessary to delete entries associated with the dbo.Gate entries. This can be done with the following SQL query, again replacing text.8600 as was done above.
DECLARE @find varchar(250)
SET @find = 'A.TestRemove';
DECLARE @ChannelID_Temp int
Set @ChannelID_temp = (SELECT ChannelID from PortChannelAffinity Where PortID in (SELECT ID FROM Port WHERE GateID in (SELECT ID FROM Gate WHERE name like @find)))
DELETE FROM connectionRequest WHERE PortID in (SELECT ID FROM Port WHERE GateID in (SELECT ID FROM Gate WHERE name like @find))
DELETE FROM Security WHERE DeviceID in (Select ID FROM Device WHERE PortID in(SELECT ID FROM Port WHERE GateID in (SELECT ID FROM Gate WHERE name like @find)))
DELETE FROM Device WHERE PortID in (SELECT ID FROM Port WHERE GateID in (SELECT ID FROM Gate WHERE name like @find))
DELETE FROM PortState WHERE PortID in (SELECT ID FROM Port WHERE GateID in (SELECT ID FROM Gate WHERE name like @find))
DELETE FROM PortChannelAffinity WHERE PortID in (SELECT ID FROM Port WHERE GateID in (SELECT ID FROM Gate WHERE name like @find))
DELETE FROM Channel WHERE ID = @ChannelID_temp
DELETE FROM Port WHERE GateID in (SELECT ID FROM Gate WHERE name like @find)
DELETE FROM Gate WHERE name like @find
for devices using the "DeleteSource_BatchDeleteDataPME2020_V01.sql" script
Download and run the script in SSMS (no need to enter any device name), this will create a stored procedure "spDM_DeleteSourceAndBatchDeleteData" in ION_Data DB that batch deletes data at 2500 rows per batch and finally the device itself.
You may call this SP to delete the device as below -
(Note: GroupName.Device name should be as per the 'Name' in the table [ION_Data].[dbo].[Source])