Tuesday, March 6, 2012

Add new primary key column for replicated table with pull subscrip

I need to add a new column to a replicated table. But in my case a new column should be a part of a primary key. So, I can't use sp_repladdcolumn procedure since we need to completely repopulate the whole table and there is no default value for a new colu
mn. In addition to that this column should be first in the table's columns list, not the last one what happens when you usually alter the table adding a new column.
More there, I have a pull subscription and it looks like there is no way to drop subscription for a specified article only, like it is possible for a push subscription. I would appreciate any help pointing to an easiest way to add a new primary key column
for a replicated table with pull subscription.
TIA
Libra,
ordinarily you should be able to use sp_droparticle, sp_addarticle and
sp_refreshsubscriptions however to add the new column in a specific position
you'll need to drop the published article then recreate it and resubscribe.
You might use a nosync subscription to make things easier if the snapshot is
particularly large, but this will have restrictions if you later want to add
an article or column, so I'd recommend initializing with the complete
snapshot as per usual.
HTH,
Paul Ibison
(BTW no doubt you have seen other threads on this but placing columns in a
particular order is not generally considered 'good form' as TSQL should
ideally use explicit column names rather than rely on position.)

No comments:

Post a Comment