Microsoft Dynamics AX (Archived)

There is already an object named I_XXXRECID in the database.

(0) Share

Report

Posted on by MYGz

2,059

Hi,

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 alter the constraint name from SSMS:

ALTER TABLE dbo.ACOCOSTCENTERATTRIBUTEVALUE_BR DROP CONSTRAINT I_100013RECID;

But getting this error:

Msg 3733, Level 16, State 2, Line 25
Constraint 'I_100013RECID' does not belong to table 'ACOCOSTCENTERATTRIBUTEVALUE_BR'.
Msg 3727, Level 16, State 0, Line 25
Could not drop constraint. See previous errors.

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

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

Output:

(No column name)
dbo.ACOCOSTCENTERATTRIBUTEVALUE_BR

On checking sys.indexes there is only one index by this name:

*This post is locked for comments

I have the same question (0)

All responses (15)

Answers (0)

Suggested answer

André Arnaud de Cal... 299,395 Super User 2025 Season 2 on at

Like (0)

Report

RE: There is already an object named I_XXXRECID in the database.

Hi MYGz,

If this is a new environment with the model and business data from the same source environment, then try to fully compile the application first (x++ and CIL). Then restart the AOS and retry the synchronization. If the business data and model are from different sources, it will probably not work as each environment has element specific IDs which might cause this issue as well.

Was this reply helpful? Yes No
Suggested answer

Mea_ 60,284 on at

Like (0)

Report

RE: There is already an object named I_XXXRECID in the database.

Check sqldictionary table in SSMS, it holds ids and AX uses it to create objects in SQL. ID of ACOCOSTCENTERATTRIBUTEVALUE_BR table and fields should be the same as in AOT, if not fix them directly in SQL.

Was this reply helpful? Yes No
MYGz 2,059 on at

Like (0)

Report

RE: There is already an object named I_XXXRECID in the database.

Hi Andre, Ievgen,

Additional information:

No conflict in table ID:

Table from SSMS:

After Deleting table from SSMS:

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:

Was this reply helpful? Yes No
Mea_ 60,284 on at

Like (0)

Report

RE: There is already an object named I_XXXRECID in the database.

So you completely ignored my message to check sqldictionary table and compare Ids?

Was this reply helpful? Yes No
MYGz 2,059 on at

Like (0)

Report

RE: There is already an object named I_XXXRECID in the database.

Hi Ievgen,

here's the sql dictionary result:

Was this reply helpful? Yes No
MYGz 2,059 on at

Like (0)

Report

RE: There is already an object named I_XXXRECID in the database.

I also ran AXBuild.exe and DataDictionary sync but to no avail. Shall I try right click AOT compile? Currently Takes 5+ hours on my system.

Was this reply helpful? Yes No
Mea_ 60,284 on at

Like (1)

Report

RE: There is already an object named I_XXXRECID in the database.

If you run next SQL statement you should be able to see table name for that index:

SELECT

SCHEMA_NAME(schema_id) AS [Schema name]

, name AS [Table name]

FROM sys.tables

WHERE object_id = 1208924670

Was this reply helpful? Yes No
MYGz 2,059 on at

Like (0)

Report

RE: There is already an object named I_XXXRECID in the database.

It's getting the same table name.

Schema name Table name

dbo dbo.ACOCOSTCENTERATTRIBUTEVALUE_BR

Would that be okay to modify the sys tables? Will synchronize from AOT recreate the correct entries again in the sys tables?

Was this reply helpful? Yes No
Suggested answer

Mea_ 60,284 on at

Like (0)

Report

RE: There is already an object named I_XXXRECID in the database.

You can try to drop the table from SSMS directly, delete all records from sqldictionary where tableid = 100013. Then check that there is no I_100013RECID constrain in SQL and sync it from AX.

Was this reply helpful? Yes No
MYGz 2,059 on at

Like (0)

Report

RE: There is already an object named I_XXXRECID in the database.

Done.

drop table ACOCOSTCENTERATTRIBUTEVALUE_BR

delete from SQLDICTIONARY where tableid=100013

But the issue persists.

The problem is when I drop the table form ssms, the entries in sys.indexes and sys.key_constraints are still there (Ideally they should have been deleted as well).

And when I sync from AOT, sys.indexes creates duplicate entry for 1 index and fails while creating for RECID.

Was this reply helpful? Yes No