When i tried to insert the new field into the ax dynamics custtable it is not synchronizing while that particular table is configured for the transactional replication----- throwing the error as—> cannot execute the data definition language command on (). the sql database has issued an error.
and problems during the sql data dictionary synchronization.
In the even viewer the error description is as bellow… Object Server 02: The database reported (session 3 (Admin)): [Microsoft][SQL Native Client][SQL Server]Only members of the sysadmin fixed server role or db_owner fixed database role can perform this operation. Contact an administrator with sufficient permissions to perform this operation… The SQL statement was: "ALTER TABLE CUSTTABLE ADD FIELD1 NVARCHAR(10) NOT NULL DEFAULT ‘’ "
But the user, whose is logged in AX and trying to add a new field is having the full rights to SQL Server.
He is having sysadmin & db_owner rights.
You cannot add tables or fields via SQL, they must be added through AX via the AOT (Application Object Tree). After the field is added, they need to make sure to synchronize the database. Synchronizing should only be performed by somebody who knows what they’re doing and they must be very careful to pay attention to errors/warnings/info’s for anything out of the ordinary.
I suspect the transactional replication set up may be causing this issue because of following reasons -
When schema changes are made from AX, I believe AX uses ‘Alter table …’ statement.
Whereas schema changes under transactional replication in SQL Server is not built to handle 'Alter table … statement. Transactional replication has got its own tools such as replication stored procedure for making schema changes.
adding new field to the ax table failed because that table is using for transactional replication that is the problem, i described the problem with error message,please read carefully then you can share your knowledge regarding this OK.
Thanks for the reply. Before configuring the replication with AX table ,I configured the transactional replication on sql server 2008R2 with with adventure works 2008 tables and i insert the column and add the data all the things i checked with sql server it is working fine. when i configured the Transactional replication with Dynamics AX custtable it is replicating the table same table up to that point it is ok. when i insert a new field to the custtable (Replication configured with this table) it is not synchronizing with sql server and throwing the error as mentioned earlier. i think you got the exact problem.
when i tried to insert the new column in dynamics ax 2009 table while that table using for snapshotreplication using sql server 2008 R2, column(new field ) is not inserting in to table. we got the same error as with transactional replication as given in my previous post.
please help me in this issue is there any other way to synchronize the new field to ax table while that table using for replication ?