Not able to add new field to the microsoft dynamics ax table while that table configured for Transactional replication

Question

When I tried to insert the new field into the Microsoft Ax dynamics custtable(table name) 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 event 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_ownerrights.

 

What could be the problem? How to resolve it?

 

Answer 1

Which version of SQL server it is on?

This error message is not native SQL server error message, please try to find the native SQL server error message.

Also, Schema changes (DDL operations) on an article are not allowed if that article is part of replication and Publication is configured for option "Replicate Schema Changes" to False.

---

Kindly mark the reply as answer if they help

Answer 2

you can make use of sp_repladdcolumn to avoid this error even though  option "Replicate Schema Changes" to False.

---

---

Kindly mark the reply as answer if they help

Answer 3

Hi Vrprasad,

Replication is preventing others from doing any changes to tables. when you are trying to alter any table through application and synchronise ,  the replication will stop to synchronise as the tables are used in replication.

So please go to those tables which you need to alter in the database, and remove those tables from replication and then synchronised. It will work.

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 4

we are using Microsoft dynamics ax 2009 and sql server 2008R2.

i tried with the publication article property "Replicate Schema Changes" to False. but it dint worked.

Answer 5

so we need to drop the replication before adding new column to the Ax table?

Answer 6

no you dont need to drop the replication. Make use of sp_repladdcolumn.

---

Kindly mark the reply as answer if they help

Answer 7

use this:

sp_repladdcolumn
@source_object = 'CUSTTABLE',
@column =  'FIELD1'
@typetext = 'NVARCHAR (10) NOT NULL DEFAULT',
@publication_to_add =  'mention your publication name here',
@force_invalidate_snapshot = 1

---

Kindly mark the reply as answer if they help

Answer 8

For SQL 2000 use sp_repladdcolumn for SQL 2005 and above you can just do an alter

ie

alter table CUSTTABLE add Field1 NVARCHAR (10) NOT NULL DEFAULT

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 9

Vrprasad,

Please see this article: Making Schema Changes on Publication Databases

It should cover most of what you need to know.

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.