D365 Operations – Table Synch error (Error : System.InvalidOperationException: Table ‘XXXXXXX’: Converting Field ‘XXXXXX’)

While development is done between team and multiple machines and you come across a case where you have to change the type of the column – you will get into error like below!

 

Table Sync Failed for Table:Table Sync Failed for Table(0,0): Error : System.InvalidOperationException: Table ‘XXXXXXX’: Converting Field ‘XXXXXX’ of Type ‘AxTableFieldReal’ to ‘AxTableFieldInt64’ is not support. Please drop the original field, sync the table and add new field with same name if needed.
AOS database sync failed. Microsoft.Dynamics.AX.Framework.Database.TableSyncException:AOS database sync failed. Microsoft.Dynamics.AX.Framework.Database.TableSyncException(0,0): Error : AggregateSyncException:Exception of type ‘Microsoft.Dynamics.AX.Data.Management.AggregateSyncException’ was thrown.

 

Just quickly wrote a script so that – I don’t have to do research each time.

DECLARE @COLUMNNAME AS NVARCHAR(100)

DECLARE @TABLENAME AS NVARCHAR(100)

DECLARE @CONSTRAINTNAME AS NVARCHAR(500)

DECLARE @PREVCONSTRAINTNAME AS NVARCHAR(500)

 

SET @COLUMNNAME = ‘COLUMNNAME’

SET @TABLENAME = ‘TABLENAME’

SET @CONSTRAINTNAME = ”

SET @PREVCONSTRAINTNAME = ”

 

DECLARE @SQL NVARCHAR(MAX)

 

WHILE 1=1

BEGIN

SELECT TOP 1 @CONSTRAINTNAME = dc.NAME, @SQL = N’ALTER TABLE [‘ + @TABLENAME + N’] DROP CONSTRAINT [‘ + dc.NAME + N’]’

FROM SYS.DEFAULT_CONSTRAINTS DC

JOIN SYS.COLUMNS C

ON C.DEFAULT_OBJECT_ID = DC.OBJECT_ID

WHERE

DC.PARENT_OBJECT_ID = OBJECT_ID(@TABLENAME)

AND C.NAME = @COLUMNNAME

 

IF @@ROWCOUNT = 0 OR @PREVCONSTRAINTNAME = @CONSTRAINTNAME BREAK;

 

IF LEN(@SQL) > 0

EXEC (@SQL)

 

SET @PREVCONSTRAINTNAME = @CONSTRAINTNAME;

END

 

SET @SQL = ‘ALTER TABLE ‘ + @TABLENAME + ‘ DROP COLUMN ‘ + @COLUMNNAME

IF LEN(@SQL) > 0 EXEC (@SQL)

 

SET @SQL=’DELETE FROM SQLDICTIONARY WHERE NAME=”’ + @COLUMNNAME + ”’ AND TABLEID = (SELECT TABLEID from SQLDICTIONARY where NAME=”’ + @TABLENAME + ”’ AND FIELDID=0)’

IF LEN(@SQL) > 0 EXEC (@SQL)

 

 

The script when executed will drop the column in question, it’s constraints and cleanup the SQLDictionary table for that field. This will allow the D365 Operations to do the database synchronize and create the column again.

 

CAUTION: Use this script based on your requirements and on the first place try to avoid the changing types at first place. As when you change type and follow the above approach you are going to loose information in that column.

There are other ways to take care of this like Creating a new column and moving the data over through X++ job and after that dropping the old column – but this requires multiple iterations to get done. The above script is quick way on development. Please use while understanding the RISK.

 

Enjoy.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.