Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP forum / GP 2018 R2 upgrade fai...
Microsoft Dynamics GP forum
Answered

GP 2018 R2 upgrade failing with tables POP30300 and POP10300 ??

(0) ShareShare
ReportReport
Posted on by Beat Bucher GP Gee... 28,009 Super User

Hi all,

I've a customer that is currently testing GP 2018R2 upgrade from 2015R2.. The initial upgrade was done in Nov. 2018 and didn't encounter much problem during the upgrade. Fast forward 6 months later and they wanted a data refresher, which was done by their IT people in restoring all the GP companies (50) and the DYNAMICS system DB.

48 out 50 companies upgraded without any issues, despite all the 3rd-party products that are installed, and which mostly upgraded their tables just fine at the first GP client launch.

However, during the GP Utilities upgrade process, 2 companies wouldn't upgrade and would keep blocking at 2 table upgrades (POP10300 & POP30300).. The most funny is that in those 2 companies, the POP module isn't used at all and the tables are empty. 

ScreenCap-2019_2D00_07_2D00_04_5F00_151304.png

What bugs me is the version numbers showing up for the tables (16.00.0580) as if GP wanted to process an intermediary update on those 2 tables .. 

ScreenCap-2019_2D00_07_2D00_04_5F00_151512.png

The system claims that a temp table should be removed, but I can't find any of those.. I've checked the GP team's blog about upgrade issues, but couldn't find anything helpful. Since the 2 companies are stuck in the upgrade process, I can't fix the tables itself, and even tried to restart the upgrade multiple times by clearing the db_upgrade table status for those 2 companies.

Any hint would be really appreciated.

Categories:
Install and Upgrade
  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,009 Super User on at
    RE: GP 2018 R2 upgrade failing with tables POP30300 and POP10300 ??

    Hi Lucas,

    I finally managed to get the 2 company DB's restored from the previous 2015 system..

    I then removed the current records related to those 2 companies from the DU000020 & DU000030 tables, as well as the DB_Upgrade table.

    Started GP Utilities and upgraded both companies without any issues !

    This was really a mess-up because the 2 companies hadn't been restored and this failed the upgrade..

    It's all good now.

    Thanks all for your input on this issue.

  • Suggested answer
    Srnvasan80 Profile Picture
    Srnvasan80 15 on at
    RE: GP 2018 R2 upgrade failing with tables POP30300 and POP10300 ??

    It seems the old version(i.e you are trying to upgrade) GP database ,view not matching with GP2018 R2 (SQL View or Tables).Take the same view or table from GP2018 R2 data base and run the alter query in your old version data base.And try the upgrade

  • Verified answer
    Lucas Miller Profile Picture
    Lucas Miller on at
    RE: GP 2018 R2 upgrade failing with tables POP30300 and POP10300 ??

    Beat,

    Within the popHistoryTransactions view the Workflow_Status column is being pulled from the POP30100 and/or POP30300 tables.  If that column is missing from those table then they didn't go through the table conversion process like they should have.

    Did you just restore the company databases back?  If so, then you may have restored them back to a point of the upgrade prior to what the DYNAMICS database believes they are at.  As a result it may have skipped the table conversion and skipped ahead to the portion where it creates the views, etc.

    I'd recommend restoring DYNAMICS and the companies back, then making sure the DU000030 table is empty before running GP Utilities again to run the upgrade again.

  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,009 Super User on at
    RE: GP 2018 R2 upgrade failing with tables POP30300 and POP10300 ??

    Hi Lucas Miller

    I had the company DB's restored from the 2015 server and re-ran the failed upgrade on those 2 companies.. This time the GP Utilities was able to pass all the tables, but failed later on (in both companies as I tried them individually) on the creation of a view : 

    pastedimage1564406435022v3.pngpastedimage1564406404749v2.png

    Which is another puzzle to add on top of this failed upgrade.. 

    The script you provided in the last reply returns no records.. but I noticed that the DB_Upgrade table holds for all companies (even the upgraded ones) entries for old version numbers related to products that are possibly no longer installed : 

    PRODID db_verMajor
    414           8
    1157        11
    1428        8
    3830       11
    6499       14
    6831       14

    Not all, but most of the companies holds the same entries.. some only references 2 products (version 14), which means they were probably created in later times. 

    What would cause the creation of the Views to fail ? I mean the workflow status fields are fairly recent and this one is related to POP module too.. 

  • 26P2ER Profile Picture
    26P2ER 1,773 on at
    RE: GP 2018 R2 upgrade failing with tables POP30300 and POP10300 ??

    Hi Beat:

    What about running the below scripts on the 2 companies

    select PONUMBER, DEX_ROW_ID from POP10110 where PONUMBER not in (select PONUMBER from POP10100)

    select PONUMBER, DEX_ROW_ID from POP30110 where PONUMBER not in (select PONUMBER from POP30100)

    Regards

  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,009 Super User on at
    RE: GP 2018 R2 upgrade failing with tables POP30300 and POP10300 ??

    Thank you very much Lucas,

    My suspiscion really goes toward the fact that those 2 DB's were stil on 2018 and not refreshed at all, whereas the DYNAMICS DB was restored with the 2015 content..

    I've asked to restore first the 2 failing DB's and if that still fails, will start looking for a DEX trace file.

  • Verified answer
    Lucas Miller Profile Picture
    Lucas Miller on at
    RE: GP 2018 R2 upgrade failing with tables POP30300 and POP10300 ??

    Hey Beat,

    For a somewhat vague upgrade error like this I would recommend capturing a DEXSQL.LOG while sending these tables through the upgrade again.  To upgrade them again you'll need to first run the following query to find the failing table records:

    SELECT b.fileOSName, a.fileNumber, a.PRODID, a.Status, a.errornum, a.errordes, c.CMPANYID, c.INTERID

         FROM DYNA)ICS.dbo.DU000030 a

         JOIN

         DYNAMICS.dbo.DU000010 b

         ON a.fileNumber = b.fileNumber

         AND a.PRODID = b.PRODID

         JOIN

         DYNAMICS.dbo.SY01500 c

         ON a.companyID = c.CMPANYID

         WHERE (a.Status <> 0 or a.errornum <> 0) and a.Status <>15

    You will need to remove the failed tables from the DU000030 table, e.g.:

    DELETE DYNAMICS..DU000030

    WHERE status in (70,135)

    Then, with a DEXSQL.LOG enabled run GP Utilities and recreate the issue.

    Within that log you'll want to search for the failed tables (POP10300 and POP30300).  At that point you kind of need to be familiar with the sequence of events for a table upgrade to know where it is going wrong.  Generally speaking the process is:

    1) Rename the table (e.g. POP10300 to P10300OP)

    2) Create a new POP10300 at the destination version structure, including all of the zDP procs and indexes

    3) Insert the data from the renamed table to the new table including default or calculated values for any new columns

    4) Drop the renamed table

    As you scroll through the log after the table is initially renamed you will want to look for any errors messages.  You'll know the process failed when it rolls back by renaming the P10300OP back to POP10300.

    Some typical issues are going to be things like bad structure for the table in the previous version, which would be a rollback after a SELECT statement against the renamed table.

    Another error we see is in reference to a zDP procedure already existing.  These should be dropped earlier in the process, but if they exist then the upgrade tries to create them again you'll need to manually delete them in SSMS before running the upgrade again.

    Hope this helps point you in the right direction,

    Lucas

  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,009 Super User on at
    RE: GP 2018 R2 upgrade failing with tables POP30300 and POP10300 ??

    Richard,

    While looking at the script to DROP & CREATE the tables that are causing issues, I just noticed something that struck me .. some of the tables have a creation date going back to Nov. 2018, when the initial upgrade took place.. so to me, this means that those 2 Company DB's havent' been restored at all per the Finance dept request on July 3rd, otherwise they would have much older creation dates on the tables..

    I'm going to request those 2 DB's to be restored again and then re-run the upgrade.

  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,009 Super User on at
    RE: GP 2018 R2 upgrade failing with tables POP30300 and POP10300 ??

    Thanks Richard,

    point 1) had already been checked, as there is indeed a mix of VersionMajor entries now that the upgrade had started for both companies.. Some modules are already on 18, but most of them still on 14. But nothing different from the original upgrade back in November, since no new modules had been added to the 2015 PROD setup, nor had there been any service packs applied..

    for point 2), the problem is that I don't have really the control of the backups and restores, as this is a huge IT environment where  I don't have a direct access to the SQL servers itself, only to an application server with SSMS on it, and thus taking backups & restores always involves to create a ticket for IT staff, though I could probably run a backup from the GP front-end directly and try to save that on a location where I have access. I'll see what I can do with that, but I'm skeptic about the fact that re-creating the tables would change anything..

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: GP 2018 R2 upgrade failing with tables POP30300 and POP10300 ??

    Beat, I would try two things here.

    1) Check the [DYNAMICS].[dbo].[DU000020] table see if these two companies have any different version numbers.

    2) For these two companies go into SQL Maintenance and drop and recreate the tables prior to trying the upgrade.

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 288,584 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,864 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans