Why does Orchestrator upgrade hinder, and roll back with error "The object _____ is dependent on column_____ .. ALTER TABLE ALTER COLUMN Column Name failed because one or more objects access this column." ?
Issue Description: Sometimes during an Orchestrator upgrade, the below error, as a result of which the upgrade hinders, and starts rolling back:
"The object 'Primary Key Constraint Name' is dependent on column 'Primary Key Column Name'. ALTER TABLE ALTER COLUMN Column Name failed because one or more objects access this column."
Root Cause and Diagnosis: This error is usually indicative of manual changes and modifications that have been done to the database that eventually lead to a disruption in data migration during the upgrade. It can also occur when the collation sequence for your DB has been manipulated.
Resolution:
- The first step would be to check the DB collation value. The collation sequence has to be the default one - "Latin1_General_CI_AS".
- If the collation is correct, then decide between 2 approaches:
- Manual changes to resolve the conflicts --> This would involve manipulations such as dropping the index in question, changing column parameters, dropping the Primary and Foreign Key constraints as needed, etc.. This is laborious and prone to many unforeseen consequences due to the heavy data manipulation needed. Any direct modification of the DB is not recommended, and these products with such modifications are not supported technically.
- Or
- Starting afresh with a new DB instance --> This would be the recommended approach towards resolution since it would ensure the robustness of the product in the long run so that one can avoid a similar situation during the future upgrades. The idea is to restore an original unmodified DB instance from the backup, point your Orchestrator to leverage the same (by changing the connection strings if needed), and then proceed with the upgrade using the same. It should be possible to surpass the DB issue, and hopefully the upgrade should proceed seamlessly.