Hi all,
Sorry, I forget to update. We had already found out how to solve this issue.
First, we did some troubleshooting by using SQL Server Profiler to trace the executed SQL transactions when DirPartyTable synchronization occurs, and then we found out these 2 statements:
CREATE TABLE "DBO".X2303X (BUSINESSACTIVITY_SA NVARCHAR(10) DEFAULT NULL ,BUSINESSACTIVITYDESC_SA NVARCHAR(60) DEFAULT NULL ,FILENUMBER_SA NVARCHAR(15) DEFAULT NULL ,COMPANYNAFCODE BIGINT DEFAULT NULL , ....... (very long statement) ............ L CHECK (RECID <> 0) )
INSERT INTO X2303X SELECT NAME,LANGUAGEID,NAMEALIAS,BUSINESSACTIVITY_SA,BUSINESSACTIVITYDESC_SA,FILENUMBER_SA,COMPANYNAFCODE,BUSINESSNUMBER_CA,SOFTWAREIDENTIFICATIONCODE_CA,FISCALCODE_IT,COMPANYTYPE_MX,RFC_MX,CURP_MX, ............ (very long statement) ...... RECVERSION,RELATIONTYPE,PARTITION,RECID FROM DIRPARTYTABLE
We found that when the synchronization occurs, AX tried to create a temporary table called "X2303X" and then copy the all contents of DirPartyTable to X2303X table using INSERT INTO .... SELECT statement. The problem is, the column order in the CREATE statement didn't same as the column order in the INSERT INTO SELECT statement. It's so right, that what Martin said, there are some field id mismatch that cause the column order is different.
So, these are steps what we did to solve this (but this is a little bit tricky and risky, and I'm not sure these will be working on different environment):
- Backup transaction & model database. We don't want to something bad happens.
- Script the DirPartyTable content using SQL Management Studio (it will generate INSERT statements to insert the table content).
- DROP the DirPartyTable table.
- Synhronize DirPartyTable from AOT. A new DirPartyTable will be create physically in the transaction database.
- Execute the INSERT statements that are generated in step 2, to fill the new DirPartyTable.
- Synchronize the whole database, restart AOS.
After this, our Dynamics AX runs normally and the synchronization error is gone.
