February 27, 2002 at 11:02 pm
I have a Transactional Replication with few articles. Now on the main database there was some change in the design of few tables. I could not find an option to make this change on the publication. I had to recreate the publication and the subscription in order to replicate the changes...is there any other solution where in I dont need to break replication in order to change the Design of objects in the article.
Jesus My Saviour
February 28, 2002 at 1:28 am
Using SQL 2000 Transactional Replication I had added a new table to the article without breaking replication.
I had to rerun the snapshot which then generated the article for the one new table only.
As for the rest I haven't tried it yet.
Regards
Steven
Steven
February 28, 2002 at 4:21 am
If you are using SQL Server 2000 you should be able to use sp_repladdcloumn or sp_repldropcloumn if the table changes where for adding and/or removing columns. There might be other SP's for other table modifications.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 28, 2002 at 5:14 am
Sorry - I have an article posted here that explains a work around.
Andy
February 28, 2002 at 5:14 am
So far there is no direct support for modifying a column of a replicated table in SQL2K, rumored for Yukon.
Andy
March 1, 2002 at 3:19 am
Thanks every one for the responses....any way let me explain my situatin a bit more brief...
I have 2 clusters. Cluster1 and Cluster2. I have an Active - Passive installation of SQL 7.0 on both the clusters... Custer1 is the Main Production server and the SQL is updated here. Now the replication happens to SQL on Cluster2. I have configured SQL on Cluster2 as the distrubutor and Cluster1 with database as Publication. Now the type of subscription is Push subscription from Sql Cluster1 to SQL Cluster2. Now I have to make some structure changes to one or few of the tables..which are published. A normal operation is reurning error (cannot modify structure because the table is published for replication) I need to break the replication in order to make changes then Create the replication again....which is tiresome and time consuming...and also risky on production Environment....hope you guys are clear....
Jesus My Saviour
March 1, 2002 at 4:31 am
I faced the same problem, but in my case the publisher and subscriber are at two different places and connected by internet. Several times I broken the replication and changed the table structure at publisher and subscriber ends. I did't get any problem till date, but make sure that you recreate the corresponding replication procedures (Insert, update and delete) again at subscriber end. All the best.
ram
Chennai
India
March 1, 2002 at 5:16 am
Sure. My article will show you how to avoid the snapshot.
http://www.sqlservercentral.com/columnists/awarren/alterreplicatedtable.asp
Andy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply