AX2012 inplace upgrade from AX2012 FTP to AX2012 R3

(0) Share

Report

Posted on by Community Member 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

All responses (10)

Answers (0)

Wvduren 1,435 on at

Like (0)

Report

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 1,435 on at

Like (0)

Report

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 1,435 on at

Like (0)

Report

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 Microsoft Employee on at

Like (1)

Report

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 1,435 on at

Like (0)

Report

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 1,435 on at

Like (0)

Report

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 Microsoft Employee on at

Like (0)

Report

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 1,435 on at

Like (0)

Report

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 1,435 on at

Like (0)

Report

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 17,788 on at

Like (0)

Report

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.