apitech
asked on
Dynamics GP 2010 Upgrade Fails on "Remove the temporary table at the database level"
Hello:
I'm doing a test upgrade from Microsoft Dynamics GP 10.0 SP5 to GP 2010 SP1. Most of the companies were upgrading successfully. But, now, two companies are getting failures in GP Utilities during database conversion on the following tables:
Account Current Summary Master (GP10110), Account Summary History (GL10111), and General Ledger Report Options Temporary (GL70501).
The Details for each of these reads: "The conversion process encountered an error and the temporary table did not get removed. Remove the temporary table at the database level."
When I run the Failed Tables script in SQL, I get the following for each of these three tables: "Table did not have the correct structure prior to the conversion of this table."
Previously, I had successfully upgraded all of these companies from GP 8.0 SP5 to GP 10.0 SP5. And, I ran DBCC CHECKDB and DBCC CHECKCATALOG prior. So, I don't know why all of a sudden I'm getting errors on table structure.
Is there a way of removing the temporary table that it speaks of in SQL, so that I can get this back on track? I stopped and started SQL and re-ran the update. But, that did no good. I got the same result.
Attached is a dexsql log if that helps.
Any ideas?
DEXSQL.LOG
I'm doing a test upgrade from Microsoft Dynamics GP 10.0 SP5 to GP 2010 SP1. Most of the companies were upgrading successfully. But, now, two companies are getting failures in GP Utilities during database conversion on the following tables:
Account Current Summary Master (GP10110), Account Summary History (GL10111), and General Ledger Report Options Temporary (GL70501).
The Details for each of these reads: "The conversion process encountered an error and the temporary table did not get removed. Remove the temporary table at the database level."
When I run the Failed Tables script in SQL, I get the following for each of these three tables: "Table did not have the correct structure prior to the conversion of this table."
Previously, I had successfully upgraded all of these companies from GP 8.0 SP5 to GP 10.0 SP5. And, I ran DBCC CHECKDB and DBCC CHECKCATALOG prior. So, I don't know why all of a sudden I'm getting errors on table structure.
Is there a way of removing the temporary table that it speaks of in SQL, so that I can get this back on track? I stopped and started SQL and re-ran the update. But, that did no good. I got the same result.
Attached is a dexsql log if that helps.
Any ideas?
DEXSQL.LOG
I have seen something similar in a recent upgrade that we did for a client to GP 2010 SP 1. We were going from GP 9.0 SP 3, to a newer patch of GP 9.0 (build 375), then to GP 2010 SP 1.
We didn't get the same exact errors about temporary tables, but the table errors we saw were about the same GL tables you list... What ended up fixing this was re-patching GP 9.0. In other words, it's not the upgrade to GP 2010 that was causing the issue in our case, it was the fact that it didn't perform the original GP 9.0 patch to build 375 completely.
Not sure if this helps and/or if you can go back and re-upgrade the problem companies to GP 10.0 SP 5 again...but if you can, it might be worth a try.
Also, you mention running DBCC CHECKDB and DBCC CHECKCATALOG prior to the upgrade. I have found throughout the years these are not as important for upgrades to go smoothly as running Check Links and Reconcile inside GP.
We didn't get the same exact errors about temporary tables, but the table errors we saw were about the same GL tables you list... What ended up fixing this was re-patching GP 9.0. In other words, it's not the upgrade to GP 2010 that was causing the issue in our case, it was the fact that it didn't perform the original GP 9.0 patch to build 375 completely.
Not sure if this helps and/or if you can go back and re-upgrade the problem companies to GP 10.0 SP 5 again...but if you can, it might be worth a try.
Also, you mention running DBCC CHECKDB and DBCC CHECKCATALOG prior to the upgrade. I have found throughout the years these are not as important for upgrades to go smoothly as running Check Links and Reconcile inside GP.
ASKER
The client once used Analytical Accounting but no longer does. So, I did not install it to upgrade it.
What my "beef" is with this situation is that all of the companies upgraded successfully in this same test environment to GP 10.0 SP5, without these errors.
Now, all of a sudden, I get these errors by upgrading to GP 2010 SP1. It's like, the companies were good enough for GP 10.0 SP5. So, why were they not good enough for GP 2010 SP1? And, the only thing that I did between the two upgrades is that I ran checklinks on the Multicurrency Setup tables and ran reconcile on the Financial series of tables for Open and History years.
I saw on one of the blogs that two or three other people had this exact same situation with upgrading to GP 2010 SP1. Unfortunately, they did not post their solution on those blogs.
What my "beef" is with this situation is that all of the companies upgraded successfully in this same test environment to GP 10.0 SP5, without these errors.
Now, all of a sudden, I get these errors by upgrading to GP 2010 SP1. It's like, the companies were good enough for GP 10.0 SP5. So, why were they not good enough for GP 2010 SP1? And, the only thing that I did between the two upgrades is that I ran checklinks on the Multicurrency Setup tables and ran reconcile on the Financial series of tables for Open and History years.
I saw on one of the blogs that two or three other people had this exact same situation with upgrading to GP 2010 SP1. Unfortunately, they did not post their solution on those blogs.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, sendow! I'll open up a case, then!
Apitech
Apitech
These types of upgrade errors, or any specific database error from Utilities, are the worst. I am not aware of any good or even remotely efficient way of troubleshooting them.
I'm guessing you could easily spend many hours trying to track down the issue, so I personally opt for a support case for these sorts of things.
But if you like a challenge and have plenty of time, you could try and troubleshoot it. Rather than try and figure out exactly what Utilities is doing when it fails, I'm thinking it might be more efficient to try and poke around the tables that you listed.
I guess I would start by querying INFORMATION_SCHEMA.COLUMNS
This portion of the Dex log seems to back up the possibility that there may be a schema difference in the tables:
/* Date: 11/13/2010 Time: 13:34:52
stmt(81008880):*/
CREATE PROC dbo.zDP_GL10111F_2 (......
/*
/* Date: 11/13/2010 Time: 13:34:52
SQLSTATE:(S0022) Native Err:(207) stmt(81008880):*/
[Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'Ledger_ID'.*/
/*
/* Date: 11/13/2010 Time: 13:34:52
SQLSTATE:(S0022) Native Err:(207) stmt(81008880):*/
[Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'Ledger_ID'.*/
Hmmm, Ledger_ID: I just read about that on a blog post. Are you using Analytical Accounting at all? Check out this post.
http://blogs.msdn.com/b/developingfordynamicsgp/archive/2010/11/12/procedure-or-function-aagcreateglworkdist-expects-parameter-ledgerid-which-was-not-supplied.aspx
Perhaps this is a new bug with the GP 2010 SP1.
If you don't want to deal with a support case, try upgrading to just GP 2010. Since you went to GP 10 SP5, I think you might need to re-do your upgrade and stop at GP 10 SP4 so that you can upgrade to GP 2010 RTM.
Thanks,
Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional