Replication: The merge process could not query the last sent and received generations.

Problem:

Topology:

- Merge replication with statically-filtered publications.

- 2 publishers, CLSILVA1 and CLSILVA2, which are publishing a different set of tables

- Many subscribers; most of them are subscribing to both publishers into the same subscriber database

- Each publisher has an individual publication for each subscriber; so each article is included in several hundred statically-filtered publications

In short...

The destination tables on the subscribers are not overlapping, meaning that the data from individual publishers go into individual tables and is not written into the same, shared table. Each publisher however has several publications sharing the same articles; but each publication is targeting a different subscriber. Each subscriber is destination for only one publication from CLSILVA1, and for only one publication from CLSILVA2.

In case of a subscriber failure, the corresponding publications and subscriptions are first dropped from both publishers and the subscriber. Afterwards the publications are re-created with the same name, followed by adding the subscriptions.

Usually it is possible to add one of the subscriptions and synchronize it. But it often fails to add the other subscriptions for the second publisher.

The error message is:

Source: Merge Replication Provider

Number: -2147200929

Message: The merge process could not query the last sent and received generations.

Category:SQLSERVER

Number: 0

Message: Row handle is invalid.

Cause / Workaround:

The reason for the failure is that information about the deleted publications and subscriptions is still present on the other replicas within the topology. When those replicas synchronize, they might transfer the outdated entries from sysmergepublications and sysmergesubscriptions to the sync partner. It might therefore happen that such an outdated entry is transferred back to the rebuilt subscriber. When synchronising this outdated row with the corresponding publisher, the pubid/subid GUIDs don't match.

Example:

Publishers CLSILVA1 and CLSILVA2, subscribers SUB1 and SUB2. SUB1 is dropped. Subscription between SUB1 and CLSILVA1 created. CLSILVA1 and CLSILVA2 synchronize with SUB2, which moves outdated SUB1 metadata to CLSILVA1 and CLSILVA2. Sync between CLSILVA1 and SUB1 moves outdated CLSILVA2-SUB1 metadata to SUB1. Now when you create the new CLSILVA2-SUB1 subscription, the outdated metadata on SUB1 meets the new, current metadata on CLSILVA2 and fails.

The solution/workaround is to run sp_mergesubscription_cleanup on publisher and subscriber before creating the subscription. This will remove the outdated metadata so that the new metadata can be inserted properly.