Correcting DB Sync errors in AX

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'

Update SQLDICTIONARY

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.

Text  

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

SELECT *
INTO tableCopy
FROM originalTable

2.     

drop table OriginalTable

3.  Perform DB sync from AX from the original Table

insert into OriginalTable select * from tableCopy

**Sometimes step 3 will not work because of the change of the fields sequence in different layers. For this use a column list in the above query. You can find all the columns of a table using below query. Migrate it to excel file and then do transpose and save it as CSV file. You will get list of columns with comma separated values and use it in the column list of above query.

select Column_name

from Information_schema.columns

where Table_name like  'DMFTAXBRANCHENTITY'