We are trying to upgrade FNMS 2018 R2 to 2022 R2 but Compliance Database migration is failing with below error:
There was an error while attempting to run 'cm-migr1710-migpost.sql'. Violation of PRIMARY KEY constraint 'PK_AssetType'. Cannot insert duplicate key in object 'dbo.AssetType'. The duplicate key value is (Mainframe). Migration step 10001, part of action set "Adding new Asset Types for Data Storage, Network Appliance and Mainframe" - Adding 3 static rows to AssetType The statement has been terminated.
Please find the logs attached. Please note logs are from second attempt.
You have a custom asset type named "Mainframe" defined.
The migration process tries to add a new built-in asset type named "Mainframe".
This fails because of the name conflict.
This is untested, but an approach to explore to see whether it works to handle this is:
Before commencing the migration, rename the custom asset type:
UPDATE AssetType
SET AssetTypeName = 'Custom-Mainframe'
WHERE AssetTypeName = 'Mainframe'
Perform the database migration
You will then end up with 2 asset types in your system: "Mainframe" and "Custom-Mainframe". If you do NOT have custom properties defined then that could be cleaned up to just use the built-in type as follows:
UPDATE a
SET AssetType = mat.AssetTypeID
FROM Asset a
JOIN AssetType mcat
ON mcat.AssetTypeID = a.AssetTypeID
AND mcat.AssetTypeName = 'Custom-Mainframe',
AssetType mat
WHERE mat.AssetTypeName = 'Mainframe'
DELETE AssetType
WHERE AssetTypeName = 'Custom-Mainframe'
If you DO have custom properties defined on your custom "Mainframe" asset type then more work would be needed to remove the custom asset type - beyond what I can easily suggest here.
(Did my reply solve the question? Click "ACCEPT AS SOLUTION" to help others find answers faster. Liked something? Click "KUDO". Anything expressed here is my own view and not necessarily that of my employer, Flexera.)
You have a custom asset type named "Mainframe" defined.
The migration process tries to add a new built-in asset type named "Mainframe".
This fails because of the name conflict.
This is untested, but an approach to explore to see whether it works to handle this is:
Before commencing the migration, rename the custom asset type:
UPDATE AssetType
SET AssetTypeName = 'Custom-Mainframe'
WHERE AssetTypeName = 'Mainframe'
Perform the database migration
You will then end up with 2 asset types in your system: "Mainframe" and "Custom-Mainframe". If you do NOT have custom properties defined then that could be cleaned up to just use the built-in type as follows:
UPDATE a
SET AssetType = mat.AssetTypeID
FROM Asset a
JOIN AssetType mcat
ON mcat.AssetTypeID = a.AssetTypeID
AND mcat.AssetTypeName = 'Custom-Mainframe',
AssetType mat
WHERE mat.AssetTypeName = 'Mainframe'
DELETE AssetType
WHERE AssetTypeName = 'Custom-Mainframe'
If you DO have custom properties defined on your custom "Mainframe" asset type then more work would be needed to remove the custom asset type - beyond what I can easily suggest here.
(Did my reply solve the question? Click "ACCEPT AS SOLUTION" to help others find answers faster. Liked something? Click "KUDO". Anything expressed here is my own view and not necessarily that of my employer, Flexera.)
