Search code examples
sql-serversql-server-2012axaptadynamics-ax-2012dynamics-ax-2012-r3

There is already an object named I_XXXRECID in the database

This error is coming while synchronizing the DataDictionary.

SQL error description: [Microsoft][SQL Server Native Client 11.0][SQL Server]There is already an object named 'I_100013RECID' in the database.

SQL statement: ALTER TABLE "DBO".ACOCOSTCENTERATTRIBUTEVALUE_BR ADD CONSTRAINT I_100013RECID PRIMARY KEY NONCLUSTERED (RECID)

I copied entire business and code database and created new AX Env. I am not sure if this error is there on source Env as well, but I want to resolve this on the new Env.

What I tried already:

  • Deleted the table from SQL Server Management Studio and then Synchronize from AOT but the error persists.

  • Tried to drop the index name from SSMS:

    DROP INDEX I_100013RECID ON [ACOCOSTCENTERATTRIBUTEVALUE_BR]

    But getting this error:

Cannot drop the index 'ACOCOSTCENTERATTRIBUTEVALUE_BR.I_100013RECID', because it does not exist or you do not have permission.

But on querying the indexes, it's showing the correct table:

select object_name(object_id) from sys.indexes WHERE name =  'I_100013RECID'

Output:

   dbo.ACOCOSTCENTERATTRIBUTEVALUE_BR
  • On checking sys.indexes there is an index by this name:

enter image description here

  • But the index is not visible in the table:

enter image description here

EDIT 1: Additional Info

No conflict in table ID:

enter image description here

Table from SSMS:

enter image description here

Deleting table from SSMS:

enter image description here

Out of 3 indexes why are the 2 indexes not getting deleted when I delete the table from SSMS? Why only 1 gets deleted? Check below for the 3 indexes after synchronization. How to get rid of these? SSMS won't let me delete it telling ' Catalog cannot be modified'. Can I try to delete it by changing the settings on master data? I'm not sure what all tables related to this table are populated in the catalog.

Synchronizing again from AOT:

enter image description here

Solution

  • Finally I got the solution:

    The problem was there were 2 tables

    [dbo].[ACOCOSTCENTERATTRIBUTEVALUE_BR]

    [dbo].[dbo.ACOCOSTCENTERATTRIBUTEVALUE_BR]

    I was dropping the table [dbo].[ACOCOSTCENTERATTRIBUTEVALUE_BR] from ssms and Synchronizing from AOT, due to which the error persisted.

    I dropped the other table [dbo].[dbo.ACOCOSTCENTERATTRIBUTEVALUE_BR] (which I didn't even knew that it existed as the tables are arranged alphabetically and I was looking only at the first table) from ssms and then synced again and it was successful.

    The second table had a prefix of "dbo." in its name. I absolutely don't have any clue how it crept in as I haven't even touched this table ever before.