Tuesday, May 5, 2015

Fix synchronization issue with fields in the AOT but not on SQL Server

Recently I was called in to help on an issue after installation of a third party module, where fields were in the AOT but wasn't synchronized to SQL Server. The fields also looked as they should in the SQLDictionary table.

New attempts to synchronize the database didn't create the fields on SQL. AX must have been under the impression that the fields were synchronized.

If I tried to delete any of the fields, I'd get an error from AX about it not being able to drop the fields on SQL Server, so I was kind of stuck.

Here is what I did to fix the situation:

  1. I looked up the SQL field names in SQLDictionary and created the fields manually on SQL Server. I was not too concerned with datatypes and other properties, as these fields were just created to be able to drop them from AX again.
  2. In the AOT I changed the SaveContents property of each field to No. This will make AX drop the fields from SQL Server.
  3. I synchronized.
  4. I changed SaveContents properties back to Yes. This will make AX create the fields again on SQL Server through the right synchronization process.
  5. I synchronized.
In the old days, this was something you could fix with SQL administration from the System administration menu, but the features to fix such issues have been broken  since version 4.0 of AX.

I never did figure out how the third party vendor brought themselves into this situation though. That would have been interesting enough to know.


gl00mie said...

"I looked up the SQL field names in SQLDictionary" - so AX created records in SQLDictionary, but not in the SQL database, didn't it?

Palle Agermark said...

Yep, they were in The SQLDictionary table, but they were not created on the tables they belong to in SQL.

gl00mie said...

Did you try to delete field records from SQLDictionary? Sometimes it helps.

Palle Agermark said...

Yes, as one of the first things :-)

Didn't help though.

Waldemar Pross said...

I had very similar situation recently (AX 2012 R3, 2 custom fields). Looked OK in SqlDictionary table too.
In my situation I was able to delete the entire table on SQL, delete the records in SqlDictionary and re-synch from AX.