Arithmetic overflow error for data type smallint, value = 32768

Issue

On upgrade of PME system, the Create Database step will fail. On inspection of the ION_Network.log, the error "Arithmetic overflow error for data type smallint, value = 32768" will be present.

Product Line

Power Monitoring Expert 2021

Power Monitoring Expert 2022

Power Monitoring Expert 2023

Power Monitoring Expert 2024

Environment

PME Installer

SQL Server Management Studio

Cause

The data type "smallint" has a maximum size of 32,768. On creation of a new custom measurement, the size constraint of the data type has been exceeded.  We can check if this is applicable by running the below query. If the value returned is 32,767 this article is applicable. This is a rare case and support should be consulted before implementing the below resolution.

SELECT MAX(ID) FROM [ION_Network].[dbo].[MM_MeasurementMap]

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.*

Custom values for default PME systems begin with ID 10,000. We limit the number of entries here to increase performance. However, we can override this by changing the datatype within the stored procedure [ION_Network].[dbo].[spMM_AddMapEntry] from smallint to int.

Int has a maximum size of  2,147,483,647. It is expected that the user will not create many more custom measurements to preserve PME performance. Removing old entries for no longer used measurements would be recommended.

1. Open  [ION_Network].[dbo].[spMM_AddMapEntry] by right clicking the stored procedure and selecting "Modify".
2. Ctrl F + Replace smallint with int.
3. Run the query.
4. Continue with the reconfiguration.