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


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)
| improve this answer | |
  • Thanks @SqlACID, do you know of any examples using the list of publications (a link would be great!) – Andrew Bickerton Aug 10 '11 at 8:42
  • @Andrew Updated with sample code – SqlACID Aug 11 '11 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 – Andrew Bickerton Nov 1 '11 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.

| improve this answer | |
  • 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 :-) – Andrew Bickerton Aug 11 '11 at 12:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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