This week i had an synchronisation error in my development environment. After retreiving changes from our Team Foundation Server some fields on the Salestable had changed. My environment could not execute these changes on the table so some manual SQL scripting was necessary.
Illegal data conversion from original field Table1.FieldX to Table2.FieldY: Unable to convert data types to anything but character field type (4 to 49).
Synchronize database Cannot execute the required database operation.The SQL database has issued an error.
(FIRST CREATE A BACKUP FROM THE DATABASE)
Open your Microsoft SQL manager and connect to you SQL server. Create a new query as below. I will use the Salestable as an example.
SELECT * INTO dbo.Salestable_BACKUP FROM dbo.Salestable
This query will make an duplicate of the salestable and copy all the records into Salestable_BACKUP.
Now delete the Salestable from your SQL database and synchronise the salestable from the AOT in the development workspace of Dynamics AX. Refresh your database in the SQL manager and the table will reappear without any of the data.
Your sync problem is solved but now we have a data issue as it is deleted from the salestable. Now we need to copy all the data back from the backup table to the freshly synchronised table. We can use the “Data import / export” wizard for this task or use a query. For this example i use the wizard.
Right click your Dynamics AX database (not the model database), click Tasks > Import data, a wizard will open.
- Choose your data source database
- Choose your data destination database
- Select copy data from one or more table or views
- Select the right source and destination table and press finish. Your records will be copied to the destination table.
Synchronise your table in Dynamics AX 2012 AOT and see if the data is viewable. After the verification of the data you can delete the SQL table via the SQL manager.