Recently when upgrading a client in a test environment from GP10 to GP2010, the upgrade failed producing the following screen shots related to a table conversion error in Product ID 949 – Field Service relating to table SVC00608:
Running the failed table upgrade script produced some of the following results.
[Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot insert the value NULL into column ‘SVC_Bill_To_Address_Code’, table ‘XXX.dbo.SVC00608’; column does not allow nulls. UPDATE fails.
Reviewing the DEXSQL log showed that SVC00608 was failing due to this update statement:
/* Date: 06/22/2012 Time: 8:20:33
stmt(45048536):*/
update SVC00608 set SVC_Bill_To_Address_Code = SVC00600.SVC_Bill_To_Address_Code from SVC00608 left join SVC00600 on SVC00600.CONSTS = SVC00608.CONSTS and SVC00600.CONTNBR = SVC00608.CONTNBR
I then ran the following script that identified an orphaned contract between the SVC00600 and SVC00608 tables.
SELECT CONTNBR from SVC00608 WHERE CONTNBR not in (select CONTNBR from SVC00600)
With these results, I backed up the table and then delete the orphaned contract from the SVC00608 table with the following scripts:
SELECT * into SVC00608bkp from SVC00608
DELETE SVC00608 where CONTNBR = ‘0000011158’
I then launched Dynamics Utilities again and the upgrade of the client’s database completed successful.
By using the test environment, I was able to provide our client with the orphaned records between the two tables so they could investigate and resolve the issue before the Go-Live upgrade date.
