Skip to main content

AX to SQL data dictionary synchronization issues

One of my clients restored their AX database from PROD to DEV environment for AX 2009 and on database sync following error message was thrown

Cannot execute a data definition language command on ().
The SQL database has issued an error.
Problems during SQL data dictionary synchronization.
The operation failed.
Synchronize failed on 1 table(s)

This error message does not tell you which table is failing during sync, when I checked from event log I found following error logs which tells you table name but error message was totally misleading.
“Object Server 01: The database reported (session 5 (#####)): [Microsoft][SQL Native Client][SQL Server]There is already an object named '<TABLENAME>' in the database” 
"Object Server 01:  The database reported (session 23 (#####)): [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot drop the table 
'<TABLENAME>', because it does not exist or you do not have permission.. The SQL statement was: "DROP TABLE <TABLENAME>"

NOTE: In my case it was MarkupTrans table

Now, let’s play around and try to find the exact issue and resolve it.
  1.  Take table properties in AOT and check ID – For me it was 30088
  2. Open SQLDictionary table from SQL Management Studio and filter it with your table name, put ‘Markuptrans’ in name column. TableId column there will tell you the ID for MarkupTrans table, for my case it was 30086 which is different as shown from AOT
  3. You need to change TableID value in SQLDictionary table same as we have in AOT – I changed it to 30088. You cannot change directly this TableId in SQLDictionary Table, either you can update it from SQL server management studio or make TableId field in SQLDictionary Table editable by changing its property AllowEdit = YES. You MUST have to change it back to previous state after updating tableId.
  4. You can also check IDs for all fields in AOT for your table, these must be same as we have in SQLDictionary Table.


Synchronize your application again – it will be all good and will not throw such errors

Labels:

Comments

  1. AnonymousJuly 7, 2015 at 5:59 PM

    Thank you for providing such a good data of the consumer as it is working good and also getting good and positive response for us and also we are expecting more data and we are looking forward for that, which helps us in providing the good service
    Microsoft Dynamics CRM Online Training | Dynamics AX Online Training

    ReplyDelete
  2. IT consultentDecember 19, 2015 at 9:45 PM

    I am faced this problem many time but still not get proper answer but this blog really help me thanks so much for sharing....

    ReplyDelete

Post a Comment

I will appreciate your comments !

Popular posts from this blog

The Dual Write implementation - Part 1 - Understand and Setup

What is Dual-write? Tightly couples – complete at one transaction level Near real time Bi-directional Master data and business documents – Customer records you are creating and modifying and at this document we are talking about sales orders or quotes and invoice. Master data could be reference data e.g. customer groups and tax information Why Dual-write and why not Data Integrator? Data Integrator is Manual or Scheduled One directional Now, Let's deep dive and understand what is required for Dual-write setup and from where to start. First thing first, check you have access to https://make.powerapps.com/ Choose right environment of CDS (CE) Make sure you have access to the environment too, click on gear icon and Admin Center  Look for required environment and Open it, you must have access as going forward you are going to configure dual write steps in the environment user the same user you are logged in now. Now, go back to power platform admin center and...
55 comments
Read more

D365FO: Entity cannot be deleted while dependent Entities for a processing group exist. Delete dependent Entities for a processing group and try again.

Scenario: There are times when you want to delete an entity from target entity list and when you do so, you face an error message which does not tell you where exactly the entity has been used.  "Entity cannot be deleted while dependent Entities for the processing group exist. Delete dependent Entities for a processing group and try again. " Solution: Browse the environment by appending this part  /?mi=SysTableBrowser&TableName=DMFDefinitionGroupEntity&cmp=USMF   at the end.  For example; if the environment URL is  https://daxture.sandbox.operations.dynamics.com then the complete URL will be https://daxture.sandbox.operations.dynamics.com/?mi=SysTableBrowser&TableName=DMFDefinitionGroupEntity&cmp=USMF Filter for Entity and it will give you the DefinitionGroup where the entity has been added or used in data management import/export projects. Get the DefinitionGroup name and search in the export/import projects under data management and either del...
4 comments
Read more

Dual-write connection set error: An item with the same key has already been added

If you happen to see this error message then you have duplicate records in cdm_company entity in CDS environment. Check for cdm_companycode field this is normally not allowed but have a look and delete the ones with duplicates.
1 comment
Read more