How can we help you today?
Power Monitoring Expert 9.0 - upgrade fails at 'Verify Database' on ION_Network database - 'duplicate key was found for the object name 'dbo.Translator'
Issue
When upgrading to PME 9.0 from an earlier PME version, the 'Verify Database' step fails on the ION_Network database upgrade due to duplicate entries in the Translator table. You will see an error in the log file something like this:
Error SQL72014: .Net SqlClient Data Provider: Msg 1505, Level 16, State 1, Line 1 The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Translator' and the index name 'AK_Translator_Name'. The duplicate key value is (BCPM).
Error SQL72045: Script execution error. The executed script:
ALTER TABLE [dbo].[Translator]
ADD CONSTRAINT [AK_Translator_Name] UNIQUE NONCLUSTERED ([Name] ASC);
Error SQL72014: .Net SqlClient Data Provider: Msg 1750, Level 16, State 1, Line 1 Could not create constraint or index. See previous errors.
Error SQL72045: Script execution error. The executed script:
ALTER TABLE [dbo].[Translator]
ADD CONSTRAINT [AK_Translator_Name] UNIQUE NONCLUSTERED ([Name] ASC);
Product Line
Power Monitoring Expert 9.0
Environment
Upgrading to Power Monitoring Expert 9.0 from PME 8.2 or earlier, whether an in-place upgrade, a CM tool Side by Side upgrade or a manual Side by Side upgrade
Cause
As of PME 9.0, a uniqueness constraint was imposed on the Name column in the Translator table in ION_Network. In some older ION_Network databases, it is possible to have duplicate entries in the Name column, usually for the BCPM, CM4000, MICROLOGIC or PM800 device types. During the upgrade to PME 9.0, imposing the uniqueness constraint fails due to the duplicate Name entries.
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.*
Method 1: Requires you to analyze the ION_Network database prior to the upgrade
Prior to the upgrade, check the Translator table in the ION_Network database for duplicates by running the following query:
USE ION_Network
SELECT * FROM Translator
ORDER BY Name
If duplicates exist, run the attached .sql called 'clean_up_Translator_table.sql'.
If this is done prior to upgrading to PME 9.0, you will not encounter this problem.
Method 2: Assumes you have run the PME 9.0 Installer to upgrade from older PME versions and the Installer stops on 'Verify Database'.
To correct this problem it is necessary to correct the Translator table. To do this, open SQL Server Management Studio (SSMS) and run the attached .sql called 'clean_up_Translator_table.sql'.
It is also necessary to drop the WindowsTimeZoneId column in the SRC_Timezone table in the ION_Network database. This can be done within SSMS:
Once this is done, if the Installer has been left running, click on 'Retry All Steps' to retry the upgrade and it will succeed.
Important: If you do not delete the WindowsTimeZoneId column as described above, prior to retrying the upgrade, and only clean up the Translator table, you will see this error:
Warning SQL72013: The following SqlCmd variables are not defined in the target scripts: ReferenceDataFilesPath.
Error SQL72014: .Net SqlClient Data Provider: Msg 5074, Level 16, State 1, Line 39 The column 'WindowsTimeZoneId' is dependent on column 'RegistryKey'.
Error SQL72045: Script execution error.
...
Error SQL72014: .Net SqlClient Data Provider: Msg 4922, Level 16, State 9, Line 39 ALTER TABLE ALTER COLUMN RegistryKey failed because one or more objects access this column.
Error SQL72045: Script execution error. The executed script:
DECLARE @CurrentSchemaVersion AS INT;
To recover, simply use SSMS to delete the column, and then 'Retry All Steps' again.
When upgrading to PME 9.0 from an earlier PME version, the 'Verify Database' step fails on the ION_Network database upgrade due to duplicate entries in the Translator table. You will see an error in the log file something like this:
Error SQL72014: .Net SqlClient Data Provider: Msg 1505, Level 16, State 1, Line 1 The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Translator' and the index name 'AK_Translator_Name'. The duplicate key value is (BCPM).
Error SQL72045: Script execution error. The executed script:
ALTER TABLE [dbo].[Translator]
ADD CONSTRAINT [AK_Translator_Name] UNIQUE NONCLUSTERED ([Name] ASC);
Error SQL72014: .Net SqlClient Data Provider: Msg 1750, Level 16, State 1, Line 1 Could not create constraint or index. See previous errors.
Error SQL72045: Script execution error. The executed script:
ALTER TABLE [dbo].[Translator]
ADD CONSTRAINT [AK_Translator_Name] UNIQUE NONCLUSTERED ([Name] ASC);
Product Line
Power Monitoring Expert 9.0
Environment
Upgrading to Power Monitoring Expert 9.0 from PME 8.2 or earlier, whether an in-place upgrade, a CM tool Side by Side upgrade or a manual Side by Side upgrade
Cause
As of PME 9.0, a uniqueness constraint was imposed on the Name column in the Translator table in ION_Network. In some older ION_Network databases, it is possible to have duplicate entries in the Name column, usually for the BCPM, CM4000, MICROLOGIC or PM800 device types. During the upgrade to PME 9.0, imposing the uniqueness constraint fails due to the duplicate Name entries.
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.*
Method 1: Requires you to analyze the ION_Network database prior to the upgrade
Prior to the upgrade, check the Translator table in the ION_Network database for duplicates by running the following query:
USE ION_Network
SELECT * FROM Translator
ORDER BY Name
If duplicates exist, run the attached .sql called 'clean_up_Translator_table.sql'.
If this is done prior to upgrading to PME 9.0, you will not encounter this problem.
Method 2: Assumes you have run the PME 9.0 Installer to upgrade from older PME versions and the Installer stops on 'Verify Database'.
To correct this problem it is necessary to correct the Translator table. To do this, open SQL Server Management Studio (SSMS) and run the attached .sql called 'clean_up_Translator_table.sql'.
It is also necessary to drop the WindowsTimeZoneId column in the SRC_Timezone table in the ION_Network database. This can be done within SSMS:
- Start SSMS
- Expand 'Databases', then the ION_Network database
- Expand 'Tables' and find the SRC_Timezone table
- Expand 'Columns', right click on the WindowsTimeZoneId column and choose 'Delete' from the popup menu
Once this is done, if the Installer has been left running, click on 'Retry All Steps' to retry the upgrade and it will succeed.
Important: If you do not delete the WindowsTimeZoneId column as described above, prior to retrying the upgrade, and only clean up the Translator table, you will see this error:
Warning SQL72013: The following SqlCmd variables are not defined in the target scripts: ReferenceDataFilesPath.
Error SQL72014: .Net SqlClient Data Provider: Msg 5074, Level 16, State 1, Line 39 The column 'WindowsTimeZoneId' is dependent on column 'RegistryKey'.
Error SQL72045: Script execution error.
...
Error SQL72014: .Net SqlClient Data Provider: Msg 4922, Level 16, State 9, Line 39 ALTER TABLE ALTER COLUMN RegistryKey failed because one or more objects access this column.
Error SQL72045: Script execution error. The executed script:
DECLARE @CurrentSchemaVersion AS INT;
To recover, simply use SSMS to delete the column, and then 'Retry All Steps' again.
Released for:Schneider Electric Australia
Attachment(s)
Explore more
Range:
Explore more
Range:
