Hi everybody,
I just wanted to pass on some information that we have gained while testing our upgrade from 9.0.8 SP14 to 11.0.0 SP1.
Maybe this will help you avoid some headaches during your upgrade process.
When going from 9.0.8 to 10.0.0 the SQL is altering the PART and PART_SITE tables to no longer allow NULL values for the STATUS column. Basically, it is looking at the PART table and changing any NULL in STATUS to ‘A’ for Active. It then looks at the PART_SITE table and updates the STATUS to the value in PART (which should be ‘A’ or ‘O’ for Obsolete).
This shouldn’t be a problem, but you will run into errors if there are any values in that STATUS column other than ‘A’, ‘O’, or NULL which is what happened to us.
I wrote the following queries to look for other values in the STATUS column and to replace them with NULL so they can be updated during the upgrade process as they should.
I hope you don’t have to use them, but if so, I hope they are helpful!
Thank you,
Andy Denton - Electronics Research, Inc.
--Return all statuses used in the PART and PART_SITE tables – if there is a status value other than ‘A’, ‘Active’, ‘O’, ‘Obsolete’, or NULL, use it in the next set of queries
SELECT DISTINCT (STATUS) FROM PART_SITE
SELECT DISTINCT (STATUS) FROM PART
--Find any parts in the PART_SITE table that have a STATUS value <> ‘A’, ‘Active’, ‘O’, ‘Obsolete’, or NULL
USE DATABASENAME
SELECT STATUS, ROWID, PART_ID FROM PART_SITE WHERE STATUS ='weird status value'
--Update STATUS for parts and replace with NULL in PART_SITE
BEGIN TRANSACTION
UPDATE PART_SITE SET STATUS = NULL WHERE STATUS = 'weird status value'
--Check that the STATUS now has a value of NULL
SELECT PART_ID, STATUS, ROWID FROM PART_SITE WHERE PART_ID IN('PART_ID1’, ‘PART_ID2’)
--COMMIT/ROLLBACK
--Find any parts in the PART table that have a STATUS value <> ‘A’, ‘Active’, ‘O’, ‘Obsolete’, or NULL
USE DATABASENAME
SELECT STATUS, ID, ROWID FROM PART WHERE STATUS = 'weird status value'
--Update STATUS for parts and replace with Null in PART
BEGIN TRANSACTION
UPDATE PART SET STATUS = NULL WHERE STATUS = 'weird status value'
--Check that the STATUS now has a value of NULL
SELECT ID, STATUS, ROWID FROM PART WHERE ID IN('PART_ID1’, ‘PART_ID2’)
--COMMIT/ROLLBACK
