*Note: Irreparable database damage can occur. These procedures should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*
*Contact technical support BEFORE any change if you are not sure how to customize the queries*
The issue:
The customer has created a custom measurement called “Dif. Temperatura Gore Mag1 (°C)”.
The measurement has already historical data and we are working with PME9. Is PC-Logging implemented on VIP
I followed the last version “PME 9.0 How to Expose Custom Measurements for Use in Web Applications” posted in the Schneider Electric Exchange Community
The problem is that I believe the character ‘°’ that is in the name does not allow the query to finish correctly.
This is the portion of code I have edited
--------------------------------------------------------------------------
-- EXAMPLE USER CODE - EDIT/COPY/PASTE THE BELOW AS NEEDED
--------------------------------------------------------------------------
INSERT INTO @Measurements VALUES('Dif. Temperatura Gore Mag1 (°C)','aggregation','average')
INSERT INTO @Measurements VALUES('Dif. Temperatura Gore Mag1 (°C)','unit','unit/degreecelsius')
INSERT INTO @Measurements VALUES('Dif. Temperatura Gore Mag1 (°C)','quantity','quantity/temperature')
INSERT INTO @Measurements VALUES('Dif. Temperatura Gore Mag1 (°C)','CDM/descriptivename','Temperature')
INSERT INTO @Measurements VALUES('Dif. Temperatura Gore Mag1 (°C)','CDM/engineeringname','Temperature')
And this is the output we get
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
Validating measurement predicate/object combinations ...
--------------------------------------------------------
Validation of measurement predicate/object combinations completed.
--------------------------------------------------------
Running in measurement update mode ...
Msg 50000, Level 16, State 1, Procedure spTS_Triples_Create, Line 38 [Batch Start Line 0]
NULL argument passed to spTS_Triples_Create.
Msg 50000, Level 16, State 1, Procedure spTS_Triples_Create, Line 38 [Batch Start Line 0]
NULL argument passed to spTS_Triples_Create.
Msg 50000, Level 16, State 1, Procedure spTS_Triples_Create, Line 38 [Batch Start Line 0]
NULL argument passed to spTS_Triples_Create.
Msg 50000, Level 16, State 1, Procedure spTS_Triples_Create, Line 38 [Batch Start Line 0]
NULL argument passed to spTS_Triples_Create.
Msg 50000, Level 16, State 1, Procedure spTS_Triples_Create, Line 38 [Batch Start Line 0]
NULL argument passed to spTS_Triples_Create.
Measurement update operations completed.
Resolution
Make a backup of the databases and VIP logic
Note2: You can run this query with the comments in place, and it will simply return the number of records found.
In this query, you need to:
Check the video below:
*Contact technical support BEFORE any change if you are not sure how to customize the queries*
The issue:
The customer has created a custom measurement called “Dif. Temperatura Gore Mag1 (°C)”.
The measurement has already historical data and we are working with PME9. Is PC-Logging implemented on VIP
I followed the last version “PME 9.0 How to Expose Custom Measurements for Use in Web Applications” posted in the Schneider Electric Exchange Community
The problem is that I believe the character ‘°’ that is in the name does not allow the query to finish correctly.
This is the portion of code I have edited
--------------------------------------------------------------------------
-- EXAMPLE USER CODE - EDIT/COPY/PASTE THE BELOW AS NEEDED
--------------------------------------------------------------------------
INSERT INTO @Measurements VALUES('Dif. Temperatura Gore Mag1 (°C)','aggregation','average')
INSERT INTO @Measurements VALUES('Dif. Temperatura Gore Mag1 (°C)','unit','unit/degreecelsius')
INSERT INTO @Measurements VALUES('Dif. Temperatura Gore Mag1 (°C)','quantity','quantity/temperature')
INSERT INTO @Measurements VALUES('Dif. Temperatura Gore Mag1 (°C)','CDM/descriptivename','Temperature')
INSERT INTO @Measurements VALUES('Dif. Temperatura Gore Mag1 (°C)','CDM/engineeringname','Temperature')
And this is the output we get
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
Validating measurement predicate/object combinations ...
--------------------------------------------------------
Validation of measurement predicate/object combinations completed.
--------------------------------------------------------
Running in measurement update mode ...
Msg 50000, Level 16, State 1, Procedure spTS_Triples_Create, Line 38 [Batch Start Line 0]
NULL argument passed to spTS_Triples_Create.
Msg 50000, Level 16, State 1, Procedure spTS_Triples_Create, Line 38 [Batch Start Line 0]
NULL argument passed to spTS_Triples_Create.
Msg 50000, Level 16, State 1, Procedure spTS_Triples_Create, Line 38 [Batch Start Line 0]
NULL argument passed to spTS_Triples_Create.
Msg 50000, Level 16, State 1, Procedure spTS_Triples_Create, Line 38 [Batch Start Line 0]
NULL argument passed to spTS_Triples_Create.
Msg 50000, Level 16, State 1, Procedure spTS_Triples_Create, Line 38 [Batch Start Line 0]
NULL argument passed to spTS_Triples_Create.
Measurement update operations completed.
Resolution
Make a backup of the databases and VIP logic
- In the VIP, rename the Arithmetic module output from “Dif. Temperatura Gore Mag1 (°C)” to “Dif. Temperatura Gore Mag1 (Deg C)”.
Close the VIP such that LogInserter can retrieve the logs and create this new measurement name.
- Confirm that the new measurement name and associated data appears in the system (dashboards)
- You will need to determine the QuantityID for this new measurement from the ION_Data database, in the Quantity table
SELECT *
FROM [ION_Data].[dbo].[Quantity]
name like '%Dif. Temperatura Gore Mag1 (Deg C)%'
FROM [ION_Data].[dbo].[Quantity]
name like '%Dif. Temperatura Gore Mag1 (Deg C)%'
- In SQL Management Studio, run the attached “Check_ION_Data for Records.sql” query to understand exactly how many records exist in the ION_Data database under “Dif. Temperatura Gore Mag1 (°C)”
You will want to note the number of records that exist for this original measurement
- Once you have the QuantityID for the original measurement, you can update the next attached query “Update_ION_Data for Records.sql”
- The update statement is commented out for safety. Once you are 100% confident in your update query with the number of records being updated and the QuantityID for the new measurement, you can uncomment lines 6, 7, and 8 and run the query
Note2: You can run this query with the comments in place, and it will simply return the number of records found.
In this query, you need to:
- Update the QuantityID to match the ID for the new measurement from Step 3 above
- Run the query with the comments in place to confirm the record count that will be updated
- Remove the comments from lines 6, 7, and 8 once you are ready to perform the update
- Rerun the “Check_ION_Data for Records.sql” query and confirm that there are now zero records under the original measurement name.
You can then quickly modify this query to check the new measurement name to confirm all historical data is now under the new measurement name.
- Check in the PME dashboard that the data appears as expected.
Check the video below:
Released for:Schneider Electric Indonesia
