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

Question

_{Thursday, February 17, 2011 7:03 AM}

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.

All replies (4)

_{Thursday, February 17, 2011 7:53 AM ✅Answered | 1 vote}

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

---

_{Friday, February 18, 2011 1:25 PM ✅Answered}

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

---

_{Thursday, February 17, 2011 8:08 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.

---

_{Friday, February 18, 2011 3:15 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