add one column to table for existing transactional replication

Question

Hi All,

Could any one explain how we can add column to table for existing transactional replication? if add column how it will come to subscriber?

---

Raveendra

Answer 1

If you have "replicate schema changes" enabled on your publisher, it will be replicated. Righ click on the publisher and click on  subscriptions options and you should see ""Replicate Schema Changes" set to true, which I belive is true by default. this takes care of the adding new columns to the replicating  tables .

you can see something like DDL change has been replicated in your replication monitor..

---

Hope it Helps!!

Answer 2

Use the alter statement,

alter table tablename add NewColumn int

If your publication is enabled to replicate ddl this will work, you can't do it through Management Studio.

---

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 3

Hello Hilary, 

I am little confused with "you can't do it through Management Studio". could please explain. Thank you sir!!!

are you implying, you can do one or the other way??

---

Hope it Helps!!

Answer 4

What he means is you must use T-SQL or SMO.  If done using the SSMS designer then SSMS attempts to drop and re-create the table which causes the schema change to fail for published objects.

This is mentioned in Make Schema Changes on Publication Databases.

Schema changes to tables must be made by using Transact-SQL or SQL Server Management Objects (SMO). When schema changes are made in SQL Server Management Studio, Management Studio attempts to drop and re-create the table. You cannot drop published objects, therefore the schema change fails.

---

Brandon Williams (blog | linkedin)

Answer 5

First off, if you add a column on a replicated table on the publisher using the new query window with alter statements the added column will be replicated to the subscriber.

Secondly I just tried using the SQL 2012 Management Studio and it worked. In the previous versions it would not and you would get an error message.

---

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 6

Thank you sir..it is clear now... 

"If your publication is enabled to replicate ddl this will work, you can't do it through Management Studio." ..I was confused if you were referring to  "replicate schema changes" cannot be changed using management studio...it is clear now...

---

Hope it Helps!!