none
can't add new column to table in replication

    Question

  • I have a merge replication running and when I try to alter table with this commancd:

    alter table mytable add cTestColumn nvarchar(10)

    getting this error:

    Msg 21531, Level 16, State 1, Procedure sp_MSmerge_altertable, Line 378
    The data definition language (DDL) command cannot be executed at the Subscriber. DDL commands can only be executed at the Publisher. In a republishing hierarchy, DDL commands can only be executed at the root Publisher, not at any of the republishing Subscribers.
    Msg 21530, Level 16, State 1, Procedure sp_MSmerge_ddldispatcher, Line 191
    The schema change failed during execution of an internal replication procedure. For corrective action, see the other error messages that accompany this error message.
    Msg 3609, Level 16, State 2, Line 1
    The transaction ended in the trigger. The batch has been aborted.

    I tried to drop the sunscription and publication and even after that I was not able to add the column or rename the table !!

    I create a test db on the same server and everythign worked fine on test db

    what is going on with my DB ? how can I get it reslved ? it's happening with almost all tables and not a specific table !!

    any hints are valuable since I'm stuck and don't know how to progress

    thank in advance,

    Patrick


    Patrick Alexander

    Thursday, June 13, 2013 1:49 AM

Answers

  • Hi,

    You need to do the schema changes in the publisher db so that the changes will be propagated to the subscriber.

    http://msdn.microsoft.com/en-us/library/ms151870.aspx

    http://msdn.microsoft.com/en-us/library/c09007f0-9374-4f60-956b-8a87670cd043


    Thanks & Regards RAJUKIRAN L Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.



    Thursday, June 13, 2013 7:02 AM
  • Hi,

    in this case if it is backup and restore, then first remove the replication from that database. Also, run sp_removedbreplication then setup the fresh replication.


    Thanks & Regards RAJUKIRAN L Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.

    Friday, June 14, 2013 3:40 AM

All replies

  • Hi,

    You need to do the schema changes in the publisher db so that the changes will be propagated to the subscriber.

    http://msdn.microsoft.com/en-us/library/ms151870.aspx

    http://msdn.microsoft.com/en-us/library/c09007f0-9374-4f60-956b-8a87670cd043


    Thanks & Regards RAJUKIRAN L Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.



    Thursday, June 13, 2013 7:02 AM
  • I tired that too, even after removing the table from publication, it doesn't allow and giving me the same error.

    Patrick Alexander

    Thursday, June 13, 2013 2:41 PM
  • As  RAJUKIRAN points out you should make this change at the publisher not the subscriber. If you are still getting this message at the publisher - are you doing republishing?

    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

    Thursday, June 13, 2013 6:00 PM
  • I'm on publisher server. what is republishing ? never heard of that ?

    This DB was on another server and we moved it to new server by back and restore and then recreate publications on new server.looks like the server thinks that its a subscriber instead of publisher !!! I can't even rename the table, even when the table is not in any publication or subscription that are shown on the SSMS

    is there any way to see if somehow these tables are stuck on any other publication or subscriptions ?


    Patrick Alexander

    Thursday, June 13, 2013 7:08 PM
  • Hi,

    in this case if it is backup and restore, then first remove the replication from that database. Also, run sp_removedbreplication then setup the fresh replication.


    Thanks & Regards RAJUKIRAN L Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.

    Friday, June 14, 2013 3:40 AM