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

3 Answers

1
MYGz On Best Solutions

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.

1
Alex On

Constraint names must be unique in the database: Can there be constraints with the same name in a DB?

Unique Constrains automatically create indexes: https://www.mssqltips.com/sqlservertip/4270/difference-between-sql-server-unique-indexes-and-unique-constraints/

This questions shows you different ways to get all constraints: SQL Server 2008- Get table constraints

2
10p On

Is table ACOCostCenterAttributeValue_BR used in your environment at all? I assume not, unless you work with Brazilian companies.

I would suggest you to

  1. Temporarily change the ConfigurationKey on the table from LedgerBasic to SysDeletedObjects63.
  2. Right-click the table, click Synchronize. It will delete the table from the SQL DB.
  3. Try to run full DB sync again, make sure there are no errors.
  4. Delete the ACOCostCenterAttributeValue_BR table from the layer you are working in. It will restore the SYS layer version of the table with ConfigurationKey = LedgerBasic.
  5. Right-click the table, click Synchronize. It will create the table in the SQL DB. If you start getting DB sync errors at that point it will mean something else is wrong in your DB e.g. another table has an index with the same name (I_100013RECID) or something like that.