Issue
When running the web-based Power Quality Report, some events are missing that appear in Vista, the RepGen version of the Power Quality Report, and the Event History Report.
Product Line
Stuxureware Power Monitoring (SPM 7.0.x)
Power Monitoring Expert (PME 7.2.x)
Environment
Power Quality Report
SQL Server Management Studio
Cause
The ION Power Quality Aggregator service processes all the Power Quality related data for sources and inserts them into the PQ_XX tables to be used in the web-based PQ report. Currently, this service will likely miss the most recent event for a source, especially if this source does not generate events very often. The 'missed' event will only be processed when a new event for that source is generated.
The problem is due to a coding issue in the stored procedure called dbo.spPQ_GetNewPQEventsForSource in the ION_Data database. In SPM, ION_Data database schema was changed so that the TimestampUTC column in the EventLog2 table now has type DATETIME2, which allows timestamps to be stored with more precision. However, there's a section of code in dbo.spPQ_GetNewPQEventsForSource that determines a time range to search for events for the given Source, and the variables that it uses to store the minimum and maximum timestamps in the time range are declared as DATETIME values. So, both minimum and maximum timestamps get truncated, which causes the most recent event will be excluded from the set of events that it returns for the Source.
For example, in a customer database, the end event for a Sag/Swell happened for a Source at 2014-04-03 17:42:55.9977452 and that event was the most recent one for the Source. However, in the time range that dbo.spPQ_GetNewPQEventsForSource was using to find events for the Source, the end date was truncated to 2014-04-03 17:42:55.997 (i.e. the milliseconds got truncated to three decimal places). Therefore, this Sag/Swell end event was outside the time range and it was not processed.
Resolution
To resolve the issue, the stored pocedure, dbo.spPQ_GetNewPQEventsForSource, must be updated.
*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.*
*Warning: Take backups of any files prior to modifying them. Ensure the backups are in a location that will not be overwritten (i.e. the Desktop).
When running the web-based Power Quality Report, some events are missing that appear in Vista, the RepGen version of the Power Quality Report, and the Event History Report.
Product Line
Stuxureware Power Monitoring (SPM 7.0.x)
Power Monitoring Expert (PME 7.2.x)
Environment
Power Quality Report
SQL Server Management Studio
Cause
The ION Power Quality Aggregator service processes all the Power Quality related data for sources and inserts them into the PQ_XX tables to be used in the web-based PQ report. Currently, this service will likely miss the most recent event for a source, especially if this source does not generate events very often. The 'missed' event will only be processed when a new event for that source is generated.
The problem is due to a coding issue in the stored procedure called dbo.spPQ_GetNewPQEventsForSource in the ION_Data database. In SPM, ION_Data database schema was changed so that the TimestampUTC column in the EventLog2 table now has type DATETIME2, which allows timestamps to be stored with more precision. However, there's a section of code in dbo.spPQ_GetNewPQEventsForSource that determines a time range to search for events for the given Source, and the variables that it uses to store the minimum and maximum timestamps in the time range are declared as DATETIME values. So, both minimum and maximum timestamps get truncated, which causes the most recent event will be excluded from the set of events that it returns for the Source.
For example, in a customer database, the end event for a Sag/Swell happened for a Source at 2014-04-03 17:42:55.9977452 and that event was the most recent one for the Source. However, in the time range that dbo.spPQ_GetNewPQEventsForSource was using to find events for the Source, the end date was truncated to 2014-04-03 17:42:55.997 (i.e. the milliseconds got truncated to three decimal places). Therefore, this Sag/Swell end event was outside the time range and it was not processed.
Resolution
To resolve the issue, the stored pocedure, dbo.spPQ_GetNewPQEventsForSource, must be updated.
*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.*
*Warning: Take backups of any files prior to modifying them. Ensure the backups are in a location that will not be overwritten (i.e. the Desktop).
- Open Windows Services and stop the following ION services:
- ION Component Identifier Service
- ION Connection Management Service
- ION Event Watcher Service
- ION Log Inserter Service
- ION Network Router Service
- ION Power Quality Aggregation Service
- ION Real Time Data Service
- ION Site Service
- ION Virtual Processor Service
- In SPM 7.0.1 stop the following service:
- Schneider Electric Service Host (CoreServicesHost)
- Schneider Electric Service Host (DataServicesHost)
- Schneider Electric Service Host (ProviderEngineHost)
- In PME 7.2.2:
- ApplicationModules CoreServicesHost
- ApplicationModules DataServicesHost
- ApplicationModules ProviderEngineHost
- In SPM 7.0.1 stop the following service:
- Save the attached SQL script titled ALTER-sqPQ_GetNewPQEventsForSource.sql onto theserver's desktop (or desired location)
- Backup the old dbo.spPQ_GetNewPQEventsForSource stored procedure
- Open SQL Server Management Studio
- Navigate to Databases > ION_Data > Programmability > Stored Procedures
- Locate and right-click on dbo.spPQ_GetNewPQEventsForSource, Select Modify
- Copy the contents of dbo.spPQ_GetNewPQEventsForSource into a text file and save to the Desktop
- Double-click the new SQL script saved in Step 2. This will populate the New Query window in SQL Server Management Studio with the new script.
- Click Execute
- Once this change has been made, start the ION services mentioned in Step 1.
Released for:Schneider Electric Ireland
