Stored procedures not updated on Subscriber

Question

We have a table in transactional replication using vertical partitioning (selective replication of columns).  When originally deployed, we ran sp_addarticle with the @vertical_partition set to 'true'.  Then we ran sp_articlecolumn for each column we wanted to replicate.  Finally, we ran sp_refreshsubscriptions.

The distribution agent ran, and the procedures for insert, update and delete on the subscriber were regenerated as expected.

A few days later, we wanted to add a new column.  so, we ran sp_articlecolum and sp_refreshsubsciptions again.  However, this time, the procedures were not re-generated.  The new column is nullable, and if we enter data on the publisher, the rows come aross to the subscriber, but with a null for that column.

What would cause the stored procs not to be regeneated?  We ended up dropping the article entirely and re-creating it with the new column, but I'd like to avoid that, if possible.

thanks

Jason

Answer 1

I typically use ALTER TABLE <Table> ADD <Column> syntax at the publisher to add a new column and include it in an existing publication.  By default the column is replicated on the next synchronization and the insert, update, and delete procedures are updated accordingly.

---

Brandon Williams (blog | linkedin)

Answer 2

Brandon,

In my case, I'm thinking of the situation where we are using vertical filtering and an existing column at the publisher is now needed at the subscriber.

I think the issues is that, since there is no schema change at the publisher, there is nothing to push the change in the article over to the subscriber.  Unless you want to reinitialize the subscription, which doesn't work for our topology (subscription is "replication_support_only"). 

I may end up needing to drop and re-add the article with the newly needed column as part of a new article for this to work, but it seems like an odd place to arrive at.  I was hoping I could just run sp_articlecolumn and the next time the distribution agent runs, the stored procs on the subscriber would be re-created.

Thanks,

Jason

Answer 3

It is the column filter.  In How to: Define and Modify a Column Filter it says to change a column filter to include additional columns that subscriptions must be reinitialized.

---

Brandon Williams (blog | linkedin)