locked
How to change datatype of a column in a table while it is Transactional Replication RRS feed

  • Question

  • Hi,

    We are using Transactional Replication running between two database in two servers. we have an update to the Source Database like modifying datatype of a particular column of a table which is already in Replication. 

    when i tried to change its datatype, it throws error

    "

    ErrorLog' table -

    Unable to modify table.  

    Cannot drop the table 'dbo.ErrorLog' because it is being used for replication.

    "

       What i have to do for the modification and i should be able to replicate the same change in Destination Server database. Please help with answers as i am new to replication

     

    Regards,
    Fazlu.

    Thursday, February 17, 2011 7:03 AM

Answers

All replies

  • You need to first make sure that you have replicate schema changes enabled. For the that go to Publication Properties -> Subscription options -> replicate schema changes , make it to true.

    Now in the publisher you can run the below command and it should make the changes in the subscriber as well.

    alter

     

    table table_name alter column column_name New_datatype

     

    regards

    Ashwin Menon

    • Proposed as answer by John Sansom Friday, February 18, 2011 3:39 PM
    • Marked as answer by WeiLin Qiao Sunday, February 27, 2011 2:00 PM
    Thursday, February 17, 2011 7:53 AM
  • Hi,

    I tried this and it is working perfectly when i run the ALTER command.... But it is throwing the same error, when i tried to change the data type in wizard. both are not same?

     Thanks for your information....

    Regads,
    Fazlu.

    Thursday, February 17, 2011 8:08 AM
  • Technically, both are same, but i assume that there might be some limitation with GUI, which frankly i am not aware of.

    Thanks,

    Ashwin

    Friday, February 18, 2011 3:15 AM
  • IIRC the GUI will attempt to drop the table and then recreate it. replication does not like this.

    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
    • Proposed as answer by John Sansom Friday, February 18, 2011 3:40 PM
    • Marked as answer by WeiLin Qiao Sunday, February 27, 2011 2:00 PM
    Friday, February 18, 2011 1:25 PM
    Answerer