SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
Search:  
 
 
        
Home       Members    Calendar    Who's On



adding column to the existing publication Expand / Collapse
Author
Message
Posted Wednesday, April 25, 2007 12:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, November 14, 2009 3:18 AM
Points: 27, Visits: 173

How can we add a column without having to reinitialize the entire publication. What is the procedure to do it?

Post #360952
Posted Thursday, April 26, 2007 8:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 11, 2007 6:21 AM
Points: 53, Visits: 1
Is this for transactional replication? If so, did you try to use sp_repladdcolumn with @force_reinit_subscription = 0. Books online (BOL) describe this as the way to disable the reinitialization (but I don't remember if it works in practice). See link below for info.

http://msdn2.microsoft.com/en-us/library/ms189464.aspx



Post #361171
Posted Thursday, April 26, 2007 10:22 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 31, 2009 3:47 PM
Points: 158, Visits: 42

Simple go to the replication properties and click on the 'Filter Columns' tab. at the bottom u would get a button named 'Add Column to table' when u click u r asked for the column name and the type also add NULL at the end because u cannot add a column with a not null constraint.

RegarDs,

JacX

Post #361432
Posted Friday, April 27, 2007 9:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, November 14, 2009 3:18 AM
Points: 27, Visits: 173
Thankyou. My publication is on sql2k and the subscriber is on sql2k5. I wish to insert column through scripts and sp_repladdcolumn might not work on different version of sql server. And before inserting a column do i have to drop subscription and the article that is published. Should i stop snapshot and distribution agent first. So what is the best way. EM or script.
Post #361586
Posted Friday, April 27, 2007 3:57 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 5:36 PM
Points: 982, Visits: 995
You must use sp_repladdcolumn if you do not want to reinitialize the publication.
Post #361706
« Prev Topic | Next Topic »


Permissions Expand / Collapse