During an upgrade to StruxureWare Power Monitoring 7.0, the upgrade to the ION_Data database fails with the following error:
"Arithmetic overflow error converting IDENTITY to data type smallint. Arithmetic overflow occurred."
The EventException table in [ION_SystemLog] will contain an error stating "Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException: Could not upgrade MSDE Database: Arithmetic overflow error converting IDENTITY to data type smallint."
ION Enterprise 6.0.1
StruxureWare Power Monitoring 7.0
Both ION_Data and ION_Network contain a table called pwrmDatabaseBuild. Many of the script files that are run to upgrade the databases contain a command that enters a row into this table. The ID column of this table is datatype smallint, which has a maximum value of 32767. The rows contained in this table will build up over time as the software is upgraded through the various versions. Once this table contains 32767 rows, the next attempt to insert a new row will fail with the error noted above. The SQL statements that will correct his problem simply clear out the table, and reseed the ID column back to 1.
This can happen on systems that have been upgraded through a number of different versions of ION Enterprise over the years.
*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).*
*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.*
Run the following query in SQL Server Management Studio, and then restart the upgrade:
Note: This table will be removed after the StruxureWarePower Monitoring 7.0.1.