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.
What could be the problem?
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.
when i tried to insert the new column in dynamics ax 2009 table while that table using for snapshot replication 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 ?