Solved

Dynamics GP 2010 Upgrade Fails on "Remove the temporary table at the database level"

Posted on 2010-11-13
5
4,000 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:apitech
  • 2
  • 2
5 Comments
 
LVL 18

Expert Comment

by:Steve Endow
ID: 34129642
Hi,

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 for the three tables you listed.  Query those tables in a database that fails, then query it in a fresh TWO database, or a database that upgrades successfully.  See if you notice any difference in fields, field types, field lengths, etc.  If no differences there, you could compare the tables in SQL Management Studio to see if the indexes, keys, and defaults are the same.

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
0
 
LVL 18

Expert Comment

by:Victoria Yudin
ID: 34130406
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.  
0
 
LVL 1

Author Comment

by:apitech
ID: 34130736
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.  
0
 
LVL 18

Accepted Solution

by:
Steve Endow earned 500 total points
ID: 34131184
Hi,

I don't think it is anything that you did, and there may not have been anything you could have done to avoid the issue.

If the error is related to the new AA Ledger ID field added to GP 2010 SP1, my guess is that the only chance you have of trying to avoid the error would be to try and upgrade to the GP 2010 RTM release.

Or, there may be some way of removing AA related setup records and tables, but it might take a while to figure that out.

Unfortunately, like I said, with Utilities errors, I default to a support case as it saves time and is relatively inexpensive compared to spending many hours on it.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional
0
 
LVL 1

Author Comment

by:apitech
ID: 34132527
Thanks, sendow!  I'll open up a case, then!

Apitech
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Automatically creating a Trello card using data from a Microsoft Dynamics CRM record turned out to be an easy project that yielded great results.  Here's how I did this for an internal team at General Code.
For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now