Sql-server – sp_repladdcolumn – is it possible to supply a list of Publications to add this column on

replicationsql serversql-server-2005

Scenario

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)

Question

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

Best Answer

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)
Related Question