Dynamics GP 10 Service pack Upgrade Failure

While upgrading from Dynamics GP 10 SP1 to SP4, one of my clients have encountered the error below.

“The stored procedure Synchronize TableData() of form duSQLAccountsynch : 27Pass Through SQL returned the following results: DBMS: 102, Microsoft Dynamics GP: 0.”

This error was surfacing while running utilities and upgrading DYNAMICS database. I’ve not seen this error before. Taking cue from the error message, I thought initially it might be due to non-synchronization of account framework so tried resetting dex.ini file to Synchronize=TRUE, but it didn’t work at all.

To understand the probable cause of this issue, I had to rely on dexsql.log file.  The log showed that there is a possible permission issue, so we tried running the GRANT.SQL and DYNSA DB owner scripts on the databases as these were databases restored from Live instance. The same issue continued.

The dexsql.log showed that the database upgrade of DYNAMICS is getting interrupted with this error when it is reaching SY10800 table specifically.

After verifying SY10800 table, We realized that the index is missing for this table.

Now the upgrade went smoother. One thing which i didn’t understand during this process is that there is another table SY10800_t that got created possibly during the upgrade by the system itself. I assume the utilities upgrade is creating a temp table which should have been deleted otherwise after successful upgrade. However, it wasn’t deleted since the upgrade is failing at this point. I compared the SY10800 and SY10800_t tables to realize both tables are having the same data while index is missing in SY10800 alone. So, to try resolve the issue, I dropped the SY10800 table from the DYNAMICS DB and then renamed the SY10800_t table as well as index to SY10800 and PKSY10800 respectively. If someone knows the exact history behind this so called “_t” tables, it’d be great if they could add their comments here.

Now tried launching the Utilities and Voila ! It worked.

BTW, I found couple of good resources while binging for the solution related to this issue though they couldn’t help much in our case. Here you go