3

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

Mohammad Yusuf
  • 16,554
  • 10
  • 50
  • 78
  • If it's an `INDEX` why are you trying to `DROP` a `CONSTRAINT`? – Thom A Apr 28 '19 at 13:36
  • @Larnu Thanks. Edited. Unable to drop the index but the index is visible in sys.indexes. – Mohammad Yusuf Apr 28 '19 at 13:41
  • What was the statement you ran, and what was the error? Don't forget, we can't see what you see – Thom A Apr 28 '19 at 13:42
  • @Larnu Updated the t-sql statement as well as the error in the question. – Mohammad Yusuf Apr 28 '19 at 13:44
  • The results show the primary key constraint already exists. To remove it, 'ALTER TABLE "dbo".ACOCOSTCENTERATTRIBUTEVALUE_BR DROP CONSTRAINT I_100013RECID`;' – Dan Guzman Apr 28 '19 at 13:46
  • @DanGuzman Yes. But the index is not visible in the table. But is visible in sys.indexes. Updated question with table indexes snapshot. – Mohammad Yusuf Apr 28 '19 at 13:50
  • Have you refreshed SSMS Object Explorer? The DMVs don't lie. – Dan Guzman Apr 28 '19 at 13:52
  • @DanGuzman MicrosoftDynamicsAX maitains indexes and tables from Application Server as well. If I delete a table from SSMS and then synchronize from Microsoft Dynamics, the table will pop back again with all the schema but minus the data. There is some conflict with application server and sql server database. – Mohammad Yusuf Apr 28 '19 at 13:56
  • @MYGz, I didn't mean to suggest you are not telling the truth, I mean the DMV query is showing what is actually in the database. So either SSMS needs to be refreshed or the query was run in a different database than the SSMS OE context. The dictionary sync is a different issue. – Dan Guzman Apr 28 '19 at 13:56
  • Have you tried a full compile and full DB sync? – Alex Kwitny Apr 29 '19 at 22:31
  • @AlexKwitny Hi Alex, I have included some additional Info, kindly check. I ran full compile with command line AXBuild.exe and then ran full datadictionary Sync. But the error persists. Shall I try right click AOT compile as well? Takes almost 5+ hours on system I'm working on. – Mohammad Yusuf Apr 30 '19 at 05:58
  • @DanGuzman Is it okay to modify sys.indexes, sys.key_constraints tables directly? It's throwing error: 'Ad hoc updates to system catalogs are not allowed.' I'm using MSSQL 2012. – Mohammad Yusuf Apr 30 '19 at 06:02
  • 1
    @MYGz, one should never modify the system tables. It is not the solution to your problem. My guess is that you already have a constraint with on a different table with the same name. Try running `SELECT OBJECT_NAME(object_id) AS TableName, * FROM sys.indexes AS I ORDER BY TableName, i.name;` – Dan Guzman Apr 30 '19 at 09:58
  • @DanGuzman Thanks. Just like you everyone suggested not to touch the sys tables. I finally got the solution with lot of discussion with other DBAs. – Mohammad Yusuf Apr 30 '19 at 11:29

3 Answers3

2

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.
10p
  • 5,488
  • 22
  • 30
1

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

Alex
  • 4,885
  • 3
  • 19
  • 39
1

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.

Mohammad Yusuf
  • 16,554
  • 10
  • 50
  • 78
  • 2
    That is bizarre. I've never seen that happen. Glad you found the issue. It's odd though that AX was apparently still trying to interact with the wrong table. Does that second one come back after a full DB sync? – Alex Kwitny Apr 30 '19 at 15:07
  • @AlexKwitny Yes especially when I have never interacted with that table before. I didn't run a full DataDictionary Sync yet. The sync error on this table is gone. I will try full DD sync and update. – Mohammad Yusuf Apr 30 '19 at 15:31
  • @AlexKwitny Hi Alex, I Synced DD again. No errors. The table with "dbo." prefix that I dropped from SSMS did not creep in again. – Mohammad Yusuf May 04 '19 at 09:49
  • The only thing I can think that may cause this is if you restored to an environment with a different kernel build OR you connected with an AX client with a different kernel build. Check `Help>About` on all the machines you were working with. Directly on the AOS you copied FROM, the one you went TO and compare those, and also any client you may have used on a terminal server. If any of the Help>About numbers are mismatched anywhere, that could be the cause. – Alex Kwitny May 05 '19 at 16:42
  • 1
    @AlexKwitny Yes, the original Env that I copied the DBs from didn't had this problem. Only the copy and the [copy of copy] had this issue. I checked kernel version and application version. It's exactly same for all 3 clients. Neverthless, I learned here that: 1. Constrain name should be unique accross the database. 2. Thou shalt not modify "sys.tables eg. sys.indexes" SQL wont let you modify on default settings anyway. 3. 'table1' and 'dbo.table1' can be separate tables. 4. Just like index, Tables can also be dropped from SSMS and synced again from AOT. So quite a bit of learning here :) – Mohammad Yusuf May 06 '19 at 06:29