Correcting DB sync errors:
Problem 1: Table Id conflict.
Lot of times we face the below error due to id conflicts during code migration. If this is the only error related to this table and you can’t help using sync option in AOT, follow the resolution step.
The SQL database has issued an error.
SQL error description: [Microsoft][SQL Server Native Client 11.0][SQL Server]There is already an object named 'DMFSTAGE' in the database.
SQL statement: CREATE TABLE "DBO".'DMFSTAGE'
Resolution: change the Table Id on SQLDictionary table
Select TABLEID,* from sqldictionary where name = 'DMFSTAGE'
set tableid = 105523 -- this is table id from AOT
from SQLdictionary where tableid = 105519 –- this is table id from SQLDictionary
Problem 2: Field Id problem
Due to field addition/deletion and movement of code in different layers, you may face below errors. In that case, the original table could be made a copy on SQL and the original table then dropped. Later sync from the AOT that will create the table and then copy the data from staging table to the target table. If the columns are changed then you need to export the data in excel and work on filter as explained below.
Illegal data conversion from original field 'DMFSTAGE'.PACKAGECODE to 'DMFSTAGE'.nextMigrate: Unable to convert data types to anything but character field type (0 to 4).
Illegal data conversion from original field 'DMFSTAGE'.SALESMANMOBILEPHONE to 'DMFSTAGE'.PurchasingDate: Unable to convert data types to anything but character field type (0 to 3).
Solution: In this case, you can perform steps 1 to 3 as mentioned below. Alternatively, correcting Fieldid on the SQLDictionary table can be performed. As a rule of thumb, the field id on AX should be copied/exist on the SQL dictionary table. I would prefer the backup and restore option always.
1. Take backup of the error table
where Table_name like 'DMFTAXBRANCHENTITY'