Skip to main content

Notifications

Microsoft Dynamics AX (Archived)

AX2012 inplace upgrade from AX2012 FTP to AX2012 R3

Posted on by Microsoft Employee

Hi,

We have started to upgrade AX2012 FTP to AX2012 R3. How ever in upgrading the database i.e. installing R3 components on database server the installation ends with below error. Has any one encountered this error before? We are not able proceed further because of this , please share your thoughts.

Cannot insert duplicate key row in object 'dbo.ModelElement' with unique index 'I_ModelElement_Id1Id2'. The duplicate key value is (42, 101120, 60001).

We have checked there are no DMF components, no upgrade models, no customisation done to standard classes like Application, Global, Info, SysSetupForm, and Session.

 

Thanks,

Ismail.

*This post is locked for comments

  • Wvduren Profile Picture
    Wvduren 1,435 on at
    RE: AX2012 inplace upgrade from AX2012 FTP to AX2012 R3

    Ok. Solved it.

    Not by dropping CUS and USR layers, but it appears that there where orphaned records in the dbo.modelelement database.

    Maybe it helps someone:

    1. Open SQL Management studio

    2. Go to AX database and to dbo.modelelement table

    3. Right click, select "Edit top 200 rows"

    4. Click on SQL button in button bar

    5. delete query that is pre-defined, and enter:

    [quote]

    SELECT     ElementType, RootHandle, ParentHandle, ElementHandle, Name, AxId, ParentId, Origin, PartOfInheritance

    FROM         ModelElement AS Element

    WHERE NOT (EXISTS (

    SELECT 'x' FROM [dbo].[ModelElementData] AS Data

    WHERE Element.ElementHandle = Data.ElementHandle

    ))

    ORDER BY Element.Name ASC

    [/quote]

    Gave no results in my case. So second SQL query:

    [quote]

    SELECT     ElementType, RootHandle, ParentHandle, ElementHandle, Name, AxId, ParentId, Origin, PartOfInheritance

    FROM         ModelElement AS Element

    WHERE     (ParentHandle <> 0) AND (NOT EXISTS

                             (SELECT     'x' AS Expr1

                               FROM          ModelElement AS Parent

                               WHERE      (ElementHandle = Element.ParentHandle))) OR

                         (RootHandle <> 0) AND (NOT EXISTS

                             (SELECT     'x' AS Expr1

                               FROM          ModelElement AS Parent2

                               WHERE      (ElementHandle = Element.RootHandle)))

    ORDER BY Name

    [/quote]

    Gave 81 records in my case.

    6. Then all orphaned elements are shown.

    7. delete them (select all, right click delete)

    8. Continue installation

  • Wvduren Profile Picture
    Wvduren 1,435 on at
    RE: AX2012 inplace upgrade from AX2012 FTP to AX2012 R3

    Permission issue solved. I checked in the database what users still had admin rights using the following query:

    select * from userinfo where id = ‘admin’

    The result was our system usr "adminax". I then executed AX as AdminAx user and it is now compiling. I will add my own user as admin later.

    So only question that remains is if I use the right sequence (Re-import CUS and USR after full compile the new environment)?

  • Wvduren Profile Picture
    Wvduren 1,435 on at
    RE: AX2012 inplace upgrade from AX2012 FTP to AX2012 R3

    Hi Ismail!

    Thank you for your help. I really appreciate it. Still two questions I hope you can answer

    So what I did so far:

    1. Created a backup of USR/CUS layer in AX 2012 R1 CU5

    2. Removed CUS / USR layer using AX Management Shell

    3. Removed old Upgrade models using AX Management Shell

    4. Uninstalled all AX components except for AOS

    5. R2 Database install (only Database): Succes!! (thanks to your tip)

    6. Reinstall AOS R2, Starts without issues

    7. Reinstall client and management utils R2

    Untill this point I have not restored the CUS and USR layers. That is correct right? I think the right moment to import the layers and tables back is after the full compile? Or should I import the layers back right after installing management utils using AX management Shell ?

    8. Now I start AX to do the upgrade checklist. Unfortunately my client shows and empty AX interface, where I cannot select anything. I also cannot go to the developers environment. When I try to open ax with '-startupcmd=kernelcompileall' it gives a message that I have insufficient rights to compile.

    It seems that AX forgot that my user is actually an admin user? Or maybe I am missing something.

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: AX2012 inplace upgrade from AX2012 FTP to AX2012 R3

    Hi Willem,

    What we did is after you have done the DB upgrade, we did not do a sync Immediately. Below steps were followed:

    1) Re imported the custom models back, with Overwrite element ID conflict. Since you have not done the sync yet, later when you sync the data would still be there. I hope you are doing code upgrade in a different environment and Data upgrade in a different environment as usual.

    Note the above step would get you new elementid's for the AX objects

    Then do the other component installation as per the Inplace Code Upgrade documentation in Tech-net.

    2) Before you do a data sync you will have to run a job to update the elementId's in SQL dictionary Table. Refer the blog from Martin Drab for this :

    dev.goshoom.net/.../id-change

    3) Then after the successful update from step 2 you can then continue with the Sync.

    Hope this helps.

    Thanks,

    Ismail.

  • Wvduren Profile Picture
    Wvduren 1,435 on at
    RE: AX2012 inplace upgrade from AX2012 FTP to AX2012 R3

    I removed the USR / CUS layers, with exception of the adjustments to the tables (extra table fields for system tables, custom tables, methods). 

    When I try to upgrade I still get the message mentioned above. So I guess I would also have to delete the USR/CUS adjustments to the tables.


    The problem is that if I remove the CUS/USR adjustments to the tables and synchronise the DB, the tablefields are dropped correct ? Then I would lose all data...? Or am I mistaken?

  • Wvduren Profile Picture
    Wvduren 1,435 on at
    RE: AX2012 inplace upgrade from AX2012 FTP to AX2012 R3

    Thank you Ismail!

    I will try the same and let you know the result.

    Kind regards, Willem.

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: AX2012 inplace upgrade from AX2012 FTP to AX2012 R3

    Hi willem,

    We had to take the backup of all the custom models from all layers. Then delete them prior to upgrading the DB. After this we tried to upgrade the DB which went fine and then again reimported the custom models with over writing element Id conflict.

    Thanks,

    Ismail.

  • Wvduren Profile Picture
    Wvduren 1,435 on at
    RE: AX2012 inplace upgrade from AX2012 FTP to AX2012 R3

    Just tried it again, but unfortunately the same result.

    Does anyone have an idea what to so next? I googled for an answer, but no results unfortunately.

    From the log:

    2016-07-21 17:57:37Z Importing file 'G:\Models\Standard\Foundation.axmodel' to the database 'LKLTEST\AXTEST_model'.
    2016-07-21 17:57:37Z Importing file 'G:\Models\Labels\FoundationLabels.axmodel' to the database 'LKLTEST\AXTEST_model'.
    2016-07-21 17:57:37Z Importing file 'G:\Models\Upgrade\FoundationUpgrade.axmodel' to the database 'LKLTEST\AXTEST_model'.
    2016-07-21 17:57:37Z Model 'Foundation' will be replaced.
    2016-07-21 17:57:37Z Model '' will be replaced.
    2016-07-21 17:57:37Z Model 'Foundation Labels' will be replaced.
    2016-07-21 17:57:37Z Model '' will be replaced.
    2016-07-21 19:02:04Z ******************************************************************************************************

    2016-07-21 19:02:04Z Cannot insert duplicate key row in object 'dbo.ModelElement' with unique index 'I_ModelElement_Id1Id2'. The duplicate key value is (42, 101120, 60001).
    2016-07-21 19:02:04Z The statement has been terminated.

    2016-07-21 19:02:04Z ******************************************************************************************************

  • Wvduren Profile Picture
    Wvduren 1,435 on at
    RE: AX2012 inplace upgrade from AX2012 FTP to AX2012 R3

    Hi all,

    I have exactly the same issue:

    Cannot insert duplicate key row in object 'dbo.ModelElement' with unique index 'I_ModelElement_Id1Id2'. The duplicate key value is (42, 101120, 60001).

     

    The SQL query above gives me no results.

    Did you solve the issue? I am upgrading AX2012 R1 CU5 to AX2012 R2.

    Thanks for your reply!!

    Kind regards, Willem.

  • Suggested answer
    Brandon Wiese Profile Picture
    Brandon Wiese 17,786 on at
    RE: AX2012 inplace upgrade from AX2012 FTP to AX2012 R3

    The element type of 42 means it's a table field.

    You can figure out which table and field exactly by running the following SQL on your model store.

    select t.Name AS TableName, f.name AS FieldName

     from ModelElement f

     join ModelElement t ON t.ElementType = 44 AND t.ElementHandle = f.ParentHandle

     where f.ElementType = 42

       and f.ElementHandle = 101120

    However, the system should not try to re-use an element ID that's already being used.

    In any case, finding out the table and field may get you one step closer to a root cause.

    Hope this helps.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Complete!

🔔 Be sure to subscribe to the new forums you are interested in to stay up to date! 🔔

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,113 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,918 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans