web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Cannot execute a data ...
Microsoft Dynamics AX (Archived)

Cannot execute a data definition language command on (). The SQL database has issued an error

(0) ShareShare
ReportReport
Posted on by hoang.nguyen83 275

Hi guys,

i have this error while synchronizing database and i have checked event view information:

Object Server 01: The database reported (session 3 (Admin)): [Microsoft][SQL Server Native Client 11.0][SQL Server]The CREATE UNIQUE INDEX statement terminated
because a duplicate key was found for the object name 'dbo.INVENTDIM' and the index name
'I_698DIMIDX'. The duplicate key value is (5637144576, rage, , , , , RAGE, RM, , In_01, , , , ,
<NULL>).. The SQL statement was: "CREATE UNIQUE INDEX I_698DIMIDX ON "DBO".INVENTDIM (PARTITION,DATAAREAID,CONFIGID,INVENTSIZEID,INVENTCOLORID,INVENTSTYLEID,INVENTSITEID,INVENTLOCATIONID,INVENTBATCHID,WMSLOCATIONID,INVENTSERIALID,INVENTGTDID_RU,INVENTPROFILEID_RU,INVENTOWNERID_RU,SHA1HASH) "

 

Please help me to correct if my understanding is wrong. The process is trying to create some key value  (5637144576, rage, , , , , RAGE, RM, , In_01, , , , , 
<NULL>), index (I_698DIMIDX) in the table (INVENTDIM) but it is impossible as they are already there.

My thought is deleting the table and it will be created itself again in synchronization process but i am not sure if there is any data in it. Is there anyway i can export the data then delete the table and after synchronization process is completed, i can import the data manually.

Please advise if there is correct way to get it right as i have not much experience on SQL and AX.

Your kind help is highly appreciated.

Regards,

Hoang

*This post is locked for comments

I have the same question (0)
  • Martin Dráb Profile Picture
    Martin Dráb 237,876 Most Valuable Professional on at

    No, it isn't creating any new values in the table. It's trying to create a unique index (as defined in AOT), but it's possible, because existing values in database aren't unique. Then the messages which values violates the unique constraint; but it doesn't mean that process is trying to create them; they're already there.

    Your idea to export the data and import it back after synchronization wouldn't fix anything, because you would still have duplicate data there.

    Please tell us what you did before you run into this problem.

  • hoang.nguyen83 Profile Picture
    hoang.nguyen83 275 on at

    Hi Martin

    Thank for quick help.

    Actually, i have no idea as i just have the system from someone else that already left.

    Is there anyway to find out or get more information in order to get it fixed.

    Regards,

    Mark

  • Martin Dráb Profile Picture
    Martin Dráb 237,876 Most Valuable Professional on at

    You have to identify the actual cause. Maybe index is wrong. For example, do you need Russia-specific fields there? Or maybe the data is incorrect and you have to fix what you have in the database.

  • hoang.nguyen83 Profile Picture
    hoang.nguyen83 275 on at

    Thanks Martin. That is my point about this system, i just have it so i am really not sure what is the root cause.

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 237,876 Most Valuable Professional on at

    You have to analyze it. I can't do it for you - even if I had access to your data, I wouldn't be able to say what's the correct setup for your business. I would need somebody to explain me the project.

    First review whether the index has the right fields. If not, fix the setup, such as by disabling configuration keys that shouldn't be enabled.

    If you think that the index is correct, look at the conflicting values mentioned in the error and find which field is causing the conflict. If it happens that you know which field is to blame but you don't how the correct setup should look like, talk to somebody familiar with the expected functionality for this client (or whatever project it is).

  • hoang.nguyen83 Profile Picture
    hoang.nguyen83 275 on at

    I actually can not find any index named I_698DIMIDX in SQL server or AOT. Please suggest if someone had solved it before.

    Thanks in advance.

    Hoang

  • Mea_ Profile Picture
    Mea_ 60,284 on at

    Hi Mark,

    It is called "DimIdx" in AX on InventDim table. You cannot find it in SQL because it does not exist and when AX tries to create it you get this error because you table contains duplicate records for this index.

  • hoang.nguyen83 Profile Picture
    hoang.nguyen83 275 on at

    Hi ievgen,

    got it. Can i manually create it or any advise, please?

    Thanks for your reply

  • Verified answer
    Mea_ Profile Picture
    Mea_ 60,284 on at

    You need to fix your data. Go to AX AOT find inventdim table then check what fields are in this index.

    Then you need to find records that has duplicate values in index fields.

    When records will be identified you need to make a decision what to do with them. fix data or delete.  

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Priya_K Profile Picture

Priya_K 4

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#3
Ali Zaidi Profile Picture

Ali Zaidi 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans