We have an existing table in 3 different publications (going to 3 different subscribers), 1 of those publications does not publish all columns.

All of these are transactional pull subscriptions.

I now need to add some more columns onto this table and we want to add them on to just the first two publications. However from MSDN:

[ @publication_to_add =] 'publication_to_add' Is the name of the publication to which the new column is added. publication_to_add is nvarchar(4000), with a default of ALL. If ALL, then all publications containing this table are affected. If publication_to_add is specified, then only this publication has the new column added.

which implies that it you expects it to specify 1 publication or ALL publications.

A sample table/publication detail to help demonstrate the issue:

Table sample

[MyTable] (
    ID int IDENTITY(1,1) NOT NULL,
    myName varchar(50) NULL,
    myDOB datetime,
    myJob varchar(100),
    mySpouseID int )

Publication/Subscriber sample

[InternalPublication] All fields, sent to InternalAdmin system
[Reporting] All fields, sent to Reporting/Datawarehouse system
[External] sent to External accessable system, fields sent:
    (ID, myName, myDOB)


How do I add on:

[myMarriageDate] datetime NULL,
[myDivorceDate] datetime NULL

and have it only added to the publications [InternalPublication] and [Reporting]?

Note: sample is a made up one to protect the innocent

2 Answers 2


sp_repladdcolumn is deprecated unless you have SQL2000 subscribers, you should use the Replicate Schema changes option on the publication. Assuming you have that set to No, I guess you could still use the sp_repladdcolumn, and if you look at the source for the proc, it looks like it has handling for accepting a comma separated list of publications, but using it is venturing into undocumented handling of a deprecated procedure...i.e. no guarantees.

sp_repladdcolumn  @source_object =  'fooTable',  
                  @column =  'fooNewColumn',  
                  @typetext =  'int' ,  
                  @publication_to_add =  '[pub1],[pub2]' 

EDIT: Publication names need to be in [] for it to work as in the code of sp_repladdcolumns it has:

select p.name, a.name, a.pubid from dbo.sysmergepublications p, dbo.sysmergearticles a   
where p.pubid=a.pubid and a.objid=@objid and p.snapshot_ready=0 
     and (p.name like @publication_to_add 
         or charindex('[' + p.name + ']', @publication_to_add)>0)
  • Thanks @SqlACID, do you know of any examples using the list of publications (a link would be great!) Commented Aug 10, 2011 at 8:42
  • @Andrew Updated with sample code
    – SqlACID
    Commented Aug 11, 2011 at 1:35
  • updated, have looked through the code for sp_repladdcolumn & there are some issues, it looks like if the pub names are in [] then it should work Commented Nov 1, 2011 at 11:46

As SqlAcid already pointed, the sp sp_repladdcolumn is already deprecated and shouldn't be used any further. From now on propagated schema changes should be used, which is anyway the default line of action.

Instead of propagating some schema changes to only a part of your subscribers, I'd say to remove the current table article from the third subscriber, create a view on the publisher that will contain only a part of the table, using only the needed columns, than add this view as an article to the third publication and not on the first two.

Might be a better solution for future changes, when you will still allow other schema changes to be propagated properly to your all 3 subscribers.

  • Thanks @Marian, nice alternative (though I don't have the option of that in this case, I will definitely consider it when it comes up again :-) Commented Aug 11, 2011 at 12:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.