Illegal data conversion from original field VendTable.ciscompanyregnum to vendtable.usecashdisc: unable to convert data types to anything but character field type (0 to 4)
During the Synchronization process in Dynamics AX 2012 there can be times when you experience errors which relate directly to SQL server statement that need to be handled and resolved. This blog has a number of tips you can use to help troubleshoot some of the more common errors. You can get Synchronization errors in the course of performing multiple tasks; Initialization checklist, full version upgrade, applying rollups, service packs or cumulative updates, moving model files or code, and in applying License key or configuration keys changes.
It is important to understand why the synchronization was triggered and if the right approach is even to continue with the synchronization or if you triggered the synchronization in error, such as missing pre-processing upgrade tasks or doing things in the wrong order.
The right approach to use in each case may be different and may not necessarily be the steps described below, but at least they may help you in investigating and understanding the underlying problem. The errors in the infolog may not contain the whole error message so get the exact details from the application event logs of the AOS server you are connected to. Normally the synchronize error is SQL statement that cannot be executed at the database and cannot be resolved without some action taken either in the database or in the AOT.
The error I will focus on is the following:
Illegal data conversion from original field VendTable.ciscompanyregnum to vendtable.usecashdisc: unable to convert data types to anything but character field type (0 to 4)
If you look at the issue at its surface the problem is that it can’t convert data types, but you need to understand this in the context of what the user is trying to accomplish. In this case they wanted to remove the SYP layer code which was from CU3, before upgrading to the Feature Pack and then upgrading to CU3. In the RTM code both columns exist, but it didn’t make sense that it would need to do a data conversion on these two fields.
To investigate this further we needed to understand why it was trying to do this seemingly incorrect conversion, but to accomplish this you need to know how the AOS determines what needs to be synchronized. The AOS looks at the AOT to see what is currently in the code and compares it to a Dynamics AX table called SQLDictionary which holds information about the state of objects that also exist in SQL server.
To get the information about the VendTable in the error we created the following SQL statement:
select * from sqldictionary where TABLEID in (select TABLEID from sqldictionary where name = 'VendTable')
This will give you the fieldids which of each object, which represent the objects ids when you look at the fields in the AOT properties.
When we checked the object id in his AOT environment in the developer workspace the id didn’t match the value we found in the SQLDictionary table
To resolve the synchronization error we changed fieldid in the SQLDictionary to match what showed in the AOT.
UPDATE SQLDICTIONARY
SET FIELDID = '60039'
WHERE SQLDICTIONARY.RECID = 5638807121;
In the course of working through the rest of the errors we found a different type of issue. This error was as follows:
Alter table DBO.CUSTINVOICELINESCRIPTBUFFER
ADD CONSTRAINT I_100412RECID Cannot add CONSTRAINT I_100412RECID because it already exists in the database
This would normally be a simple case of finding the Constraint or Index in SQL management studio on the CUSTINVOICELINESCRIPTBUFFER table and deleting it, so the synchronize could add it back, but when we checked that constraint or index didn’t exist on the CUSTINVOICELINESCRIPTBUFFER table, so we had to use a different approach.
Since we knew we could trust the SQL error message we checked the SQL server system tables to help identify the problem with the following statement.
select * from sysobjects where id in (select parent_obj from sysobjects where name = 'I_100412RECID')
This will give you the name of the SQL table where that Constraint or Index is already in use; in this case it was on an USR layer object that was removed from the environment, but where the underlying table still existed in SQL. In this case we just dropped the custom table as it was no longer in the AOT.
If you are getting synchronization errors that do no make sense or that you think you should have already resolved there may be old records left in the SQLsyncinfo table or have old or incorrect object ID references. If you suspect this make a database backup, shutdown the AOS, in SQL run truncate table SQLSYNCINFO, start the AOS and run a new Synchronize from the AOT.
Hopefully this will give you an understanding of some troubleshooting steps that you can use but you need to be careful about changing things in the SQL server database and the SQLDictionary table in a production system. Although we were able to resolve the problems using the steps above the correct method may have been to do a full X++ compile, full CIL generation, and database synchronization after removing the model files and before attempting the feature pack upgrade.
Always make sure that you are testing these steps against a copy of the production database in a test environment and that you have a good SQL backup. You want to sure that even in the test environment you can restore to a backup in case you take the wrong approach and so that you can perfect your steps before making any changes against the production database
Happy DAXing
............
Example 1a: Delete_From to Delete Multiple Records