SQL2008 merge replication fails to update depdendent items when table is added
- by Dan Puzey
Setup: an existing SQL2008 merge replication scenario. A large server database, including views and stored procs, being replicated to client machines.
What I'm doing:
* adding a new table to the database
* mark the new table for replication (using SP_AddMergeArticle)
* alter a view (which is already part of the replicated content) is updated to include fields from this new table (which is joined to the tables in the existing view). A stored procedure is similarly updated.
The problem: the table gets replicated to client machines, but the view is not updated. The stored procedure is also not updated.
Non-useful workaround: if I run the snapshot agent after calling SP_AddMergeArticle and before updating the view/SP, both the view and the stored procedure changes correctly replicate to the client.
The bigger problem: I'm running a list of database scripts in a transaction, as part of a larger process. The snapshot agent can't be run during a transaction, and if I interrupt the transaction (e.g. by running the scripts in multiple transactions), I lose the ability to roll back the changes should something fail.
Does anyone have any suggestions? It seems like I must be missing something obvious, because I don't see why the changes to the view/sproc wouldn't be replicating anyway, regardless of what's going on with the new table.
