Unable To Upgrade Planning Application: Error The object XXXX is dependent on column 'ESS_DEF_PSWD'

Here is one of the issues I have faced during Planning Application upgrade and how I was able to resolve that.

I have restored Replica application which was from version 11.1.1.3 to an 11.1.2.2 environment, now when I tried to upgrade a Planning application from 11.1.1.3 to 11.1.2.2 I got  the following error in the logs(Location of upgrade logs: Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\planning):
[FMWGEN][SQLServer JDBC Driver][SQLServer]The object 'DF__HSP_SYSTE__ESS_XXXX' is dependent on column 'ESS_DEF_PSWD'.

On investigating further I was able to found that Planning is able to execute all the steps till line 70, line number 71 in the upgrade script is:
ALTER TABLE HSP_SYSTEMCFG ALTER COLUMN ESS_DEF_PSWD VARCHAR(30)

BTW you can find the upgrade scripts at: Oracle\Middleware\EPMSystem11R1\products\Planning\config\sql

When I tried executing above ALTER query directly on database I got following:
Msg 5074, Level 16, State 1, Line 2
The object 'DF__HSP_SYSTE__ESS_D__1273C1CD' is dependent on column 'ESS_DEF_PSWD'.
Msg 4922, Level 16, State 9, Line 2
ALTER TABLE ALTER COLUMN ESS_DEF_PSWD failed because one or more objects access this column.

So Planning is not able to modify ESS_DEF_PSWD because of the constraint.  There are lots of constraints in the HSP_SYSTEMCFG table, one of them is 'DF__HSP_SYSTE__ESS_D__1273C1CD'

Here is the constraint:
GO
ALTER TABLE [dbo].[HSP_SYSTEMCFG] ADD  DEFAULT ('password') FOR [ESS_DEF_PSWD]
GO

This constraint is on the ESS_DEF_PSWD column that is why we are not able to modify that, Simple solution is to delete the constraint run the upgrade again. If you wish you can create this constraint again.

There is one more simpler way to get rid of this issue, I am sure you will be able to crack that.


Cheers..!!
Rahul S.