Connot execute a data definition language command on in ax 2009 / Force Synchronisation of AOT tables through X++ in AX:

Synchronize failed on 1 table(S) in ax 2009

static void forceDbSynchronize(Args _args)
{
    Dictionary              dict;
    int                     idx, lastIdx, totalTables;
    TableId                 tableId;
    Application             application;
    SysOperationProgress    progress;
    StackBase               errorStack;
    ErrorTxt                errorTxt;
    ;

    application = new Application();
    dict = new Dictionary();
    totalTables = dict.tableCnt();
    progress = new SysOperationProgress();
    progress.setTotal(totalTables);
    progress.setCaption("@SYS90206");
    errorStack = new StackBase(Types::String);

    lastIdx = 0;
    try
    {
        for (idx = lastIdx+1; idx <= totalTables; idx++)
        {
            tableId = dict.tableCnt2Id(idx);
            progress.setText(dict.tableName(tableId));

            lastIdx = idx;
            application.dbSynchronize(tableId, false, true, false);
            progress.incCount();
        }
    }
    catch (Exception::Error)
    {
        errorTxt = strFmt("Error in table '%1' (%2)", tableId, dict.tableName(tableId));
        errorStack.push(errorTxt);
        retry;
    }

    setPrefix("@SYS86407");
    errorTxt = errorStack.pop();
    while (errorTxt)
    {
        error(errorTxt);
        errorTxt = errorStack.pop();
    }
}

--------
AX to SQL data dictionary synchronization issues
One of my clients restored their AX database from PROD to DEV environment for AX 2009 and on database sync following error message was thrown

Cannot execute a data definition language command on ().
The SQL database has issued an error.
Problems during SQL data dictionary synchronization.
The operation failed.
Synchronize failed on 1 table(s)

This error message does not tell you which table is failing during sync, when I checked from event log I found following error logs which tells you table name but error message was totally misleading.
“Object Server 01: The database reported (session 5 (#####)): [Microsoft][SQL Native Client][SQL Server]There is already an object named '<TABLENAME>' in the database”
"Object Server 01:  The database reported (session 23 (#####)): [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot drop the table
'<TABLENAME>', because it does not exist or you do not have permission.. The SQL statement was: "DROP TABLE <TABLENAME>"

NOTE: In my case it was MarkupTrans table

Now, let’s play around and try to find the exact issue and resolve it.
 Take table properties in AOT and check ID – For me it was 30088
Open SQLDictionary table from SQL Management Studio and filter it with your table name, put ‘Markuptrans’ in name column. TableId column there will tell you the ID for MarkupTrans table, for my case it was 30086 which is different as shown from AOT
You need to change TableID value in SQLDictionary table same as we have in AOT – I changed it to 30088. You cannot change directly this TableId in SQLDictionary Table, either you can update it from SQL server management studio or make TableId field in SQLDictionary Table editable by changing its property AllowEdit = YES. You MUST have to change it back to previous state after updating tableId.
You can also check IDs for all fields in AOT for your table, these must be same as we have in SQLDictionary Table.


Synchronize your application again – it will be all good and will not throw such errors