Notifications
Clear all
Topic starter
If you have custom tables in your database, and those tables have columns with a default value, the default value will get cleared when you do a CPS upgrade. If you have routines or INSERT statements that depend on the default column values, those routines or statements will fail. This happened to us when upgrading from CPS 10 -> 12.0.9, and from CPS 12.0.9 -> SP 11.
Here are some SQL routines that you can use to minimize the pain.
Run this before and after your upgrade and compare the difference in output to determine which default values were dropped:
SELECT SO.NAME AS "Table Name", SC.NAME AS "Column Name", SM.TEXT AS "Default Value"
FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id
LEFT JOIN dbo.syscomments SM ON SC.cdefault = SM.id
WHERE SO.xtype = 'U'
AND SM.TEXT IS NOT NULL
ORDER BY SO.[name], SC.colid
Use a statement like this to recreate a default column value. You can make one sql file and recreate all dropped values at once:
ALTER TABLE cusMyCustomTable ADD DEFAULT (getdate()) FOR MyDatetimeColumn
ALTER TABLE cusMyCustomTable ADD DEFAULT ((0)) FOR MyBooleanColumn
Posted : December 29, 2015 7:47 am
Thanks for the PSA I hadn't heard this or seen it but I'll keep an eye out. For the most part I've moved any custom tables into their own databases. That way you don't have to worry about stuff like this.
Posted : January 4, 2016 5:38 pm
