Monday 23 February 2015

cannot execute a data definition language command on (). the sql database has issued an error

Hi friends;
As i was loading ax license file,i came across the above error.This error was occurring when tables were being synchronized.
I there figured out that this was a synchronization issue......

I therefore run the below job which helped me to identify the tables which could not be synchronized.
.....//Job

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();
    }
}

This job will help you identify the tables but it wont resolve the issue::

Solution:
I therefore employed a trick though it came after trying so many options...

This is how you go about this issue,

  1. Identify tables using the above code
  2. Go the the AX database in sql server management studio
  3. Rename all those table as old  example " abc.old"
  4. After renaming all those tables, go back into ax AOT then try to synchronize specific tables one by one.
  5. Note,some tables will synchronize others will give you an error-dont worry of this.
  6. After the above process,go back to the sql management studio then to your database
  7. You will notice new tables have been created with the names similar to those names you changed(without .old).
  8. Now,delete all those tables,then RENAME the tables you changed earlier back to there original names.
  9. After this process,now go back to AX(Administartion-periodic-sql administration-All tables-check/synchronize)
  10. This will resolve your issue..
           Thanks and keep reading for me ...















Friday 6 February 2015

X++ Code to Get the current company language

static void GetCompanyLanguage(Args _args)
{

str language;

language = companyinfo::languageId();

info(language);
}