You’re offline. This is a read only version of the page.
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
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
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)?
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.
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 :
3) Then after the successful update from step 2 you can then continue with the Sync.
Hope this helps.
Thanks,
Ismail.
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?
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.
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 ******************************************************************************************************
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.
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.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,113
Super User 2024 Season 2
#2
Martin Dráb
229,918
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (