Microsoft Dynamics AX (Archived)

There is already an object named I_XXXRECID in the database.

(0) Share

Report

Posted on by MYGz

1,858

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

All responses (15)

Answers (0)

MYGz 1,858 on at

Like (0)

Report

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

Hi Ievgen,

I got the solution finally, it wasn't related to permission as both the tables belong to "dbo" schema as returned in your query.

The problem was there were 2 tables

[dbo].[ACOCOSTCENTERATTRIBUTEVALUE_BR]

[dbo].[dbo.ACOCOSTCENTERATTRIBUTEVALUE_BR]

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

I dropped the other table [dbo].[dbo.ACOCOSTCENTERATTRIBUTEVALUE_BR] from ssms and then synced again and it was successfull.

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.
Mea_ 60,278 on at

Like (0)

Report

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

Then it should be easy if you have 2 DBs. Compare permissions and schema owner between two and you should be bale to find the difference.
MYGz 1,858 on at

Like (0)

Report

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

Hi Ievgen,

I was discussing this with other DBAs and also earlier you were pointing to the correct direction. The issue seems to be in the database scheme permission maybe? I'm caught up with other task, will check on this later on.

For the same table duplicate indexes are being created. But on examining the object ids, the newer object is trying to get created with a different schema and not under the same "dbo." scheme.

Check this:

Check the object names:

Your query:
Mea_ 60,278 on at

Like (0)

Report

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

Catalog error is probably related to full text search catalog. Go to SSMS under DB -> Storage -> Full text Catalogs and check that permissions are the same between 2 databases. Also you may try to exclude the table from the catalog.
MYGz 1,858 on at

Like (0)

Report

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

WOW. I tried this on another Environment and the entries from sys.indexes and sys.check_constraints also gets deleted.

But it's not happening on current Environment.
MYGz 1,858 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.
Suggested answer

Mea_ 60,278 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.
MYGz 1,858 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?
Mea_ 60,278 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
MYGz 1,858 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.