Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

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)
share|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
add comment

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.

share|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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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