web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Synchronization issue ...
Microsoft Dynamics AX (Archived)

Synchronization issue on our Dynamics 4.0 application on the database(transactional replication ) level

(0) ShareShare
ReportReport
Posted on by Hassan Jahouhi 60

Hi,

 

We are currently having a synchronization issue on our Dynamics 4.0 application where we recently setup replication on the database level. It is a simple transactional replication to a read-only database for reporting.

However, when we are now synchronizing the application with the database, we getting the below error message. After some investigation, we found out that Dynamics wants to drop and recreate some tables where modifications in the data structure took place, as well as some views. Since these tables are now part of a replicated database, it is not possible to drop them unless they are removed as replicated articles on the database level.

We would like to know how we can force Dynamics not to drop objects but instead pass the ‘Alter Statement’ to the database which is acceptable in a replicated environment.

Attached is a whitepaper from Microsoft dated 2007, whereby it states dynamics 4.0 is supported for transactional replication.

Error      Synchronize       Cannot execute a data definition language command on  ().

The SQL database has issued an error.

Error      Synchronize       Cannot execute a data definition language command on  ().

The SQL database has issued an error.

Error      Synchronize       Cannot execute a data definition language command on  ().

The SQL database has issued an error.

Error      Synchronize       Cannot execute a data definition language command on  ().

The SQL database has issued an error.

Error      Synchronize       Problems during SQL data dictionary synchronization.

The operation failed.

Info       Synchronize       Synchronize failed on 4 table(s)

 

Application: Dynamics 4.0 SP1

Database: SQL Server 2005 EE SP3 x64-bit

Setup: Transactional Replication to a standy-by database

 

Whitepaper:

High AVAILABILITY (HA) Options for Dynamics AX

Date: November 17, 2007

*This post is locked for comments

I have the same question (0)
  • Martin Dráb Profile Picture
    Martin Dráb 237,813 Most Valuable Professional on at

    Why AX wants to drop and recreate whole tables? I normally encounter it only when a table ID has been changed, and it can be resolved by updating the SqlDictionary table with the new table ID.

    BTW I use the following script in AX4 to get names of tables with synchronization errors:

    Dictionary dict = new Dictionary();
TableId tableId;
;

setPrefix("DB synchronization");
tableId = dict.tableNext(0);

while (tableId)
{
    setPrefix(tableId2Name(tableId));
    try
    {
        appl.dbSynchronize(tableId, false);
    }
    catch {}
    tableId = dict.tableNext(tableId);
}
  • Hassan Jahouhi Profile Picture
    Hassan Jahouhi 60 on at

    Hi Martin,

    Thanks for your response. The tableid is not the issue.

    The issue, with the replicate database, is when you want to add new field to an existing table, Dynamics AX generate the sql statement(by methode "application.dbsynchronize") for sql server 2005 that drops the table and recreate the table with the new field. Because of dropping the table, the replicate database will generate the following error message:

    Error      Synchronize       Cannot execute a data definition language command on  ().

    The SQL database has issued an error.

    When using an "Alter table" sql statement, it is possible to add a new field to an existing table, without dropping the table. There can be a right reason to not use the Alter table sql statement.

    Is there a solution, in case of adding new fields to an existing table,  to implement the Alter table sql statement in Dynamics Ax?

  • Martin Dráb Profile Picture
    Martin Dráb 237,813 Most Valuable Professional on at

    [quote user="Hassan Jahouhi"]Dynamics AX generate the sql statement(by methode "application.dbsynchronize") for sql server 2005 that drops the table and recreate the table[/quote]

    AFAIK this occurs only if the table ID is changed - the table is dropped, data are lost and the new table (same name, but different ID) is created . If only a new field is added, but the table ID is not changed, the table is altered and data are preserved. How data could be presserved if the table was dropped?

  • Suggested answer
    Hassan Jahouhi Profile Picture
    Hassan Jahouhi 60 on at

    [quote user="Martin Dráb"]How data could be presserved if the table was dropped?[/quote]

     

    Hi Martin,

    In case of adding new field to a table, the following will be taken place in sql server 2005:

    1 -Before the table is dropped, the data will be first copied to the temporary table.

    2 -Table will be recreated with new field.

    3 -Data will be copied from temporary table to the recreated table with new field.

    4-Temporary table wil be dropped.

    Because we are only recreating new field to an existing table, the alter sql statement

    can be use. This statement adds only the field to an existing table, and will not drop a table.

    Best regards,

    Hassan.

  • Martin Dráb Profile Picture
    Martin Dráb 237,813 Most Valuable Professional on at

    OK Hassan, I understand. I've never gone so deep... Thank you for this information, unfortunately I have no information for you. :-(

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Priya_K Profile Picture

Priya_K 4

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#3
Scott_itD Profile Picture

Scott_itD 2 Community Manager

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans