Merge replication stored procedure resolver problems

What this check looks for

One or more merge replication articles that use a custom stored procedure conflict resolver, where that resolver has either failed in the last two hours or is written in a way that is known to cause the error:

The Stored Procedure Resolver requires the stored procedure to return a result set whose row identifier matches the row identifier passed in to the resolver.

There are two kinds of finding, and they come from different places.

Recent resolver errors are read from the distribution database, so they are reported only when this instance is the distributor. Each error is checked against the current state before it is reported:

Resolver code checks read each resolver proc on the publisher. They are pattern based and flag risky code. They do not prove the proc is broken.

Why it matters

When a resolver fails, the merge agent does not move on. The resolver is asked to settle one conflicting row. If it returns no rows, more than one row, a row with a different rowguid, or fails partway, the agent tries the same conflict again on the next sync, and the one after. The subscription stops making progress, and every change queued behind that row waits with it.

That makes a failing resolver an availability problem, which is why the check is High. Nothing on the publisher looks unhealthy. The agent is running, the job is enabled, and the only symptom is a subscriber that falls further behind.

The most common causes:

• A comparison with no NULL handling, so a NULL on one side takes the branch that returns no row.
• A row that does not exist on one side, because it was deleted, filtered out, or a related row has not merged yet.
• SELECT INTO copying an identity column and then INSERT ... SELECT * failing, so the proc never returns a result set.
• A missing or disabled linked server to the subscriber, when the proc reads it through @subscriber.

How to confirm it yourself

Read the recent merge agent errors on the distributor:

USE distribution;

SELECT TOP (50) e.time, e.error_code, a.publication, a.subscriber_name, e.error_text
FROM dbo.MSrepl_errors   AS e
JOIN dbo.MSmerge_history AS h ON h.error_id = e.id
JOIN dbo.MSmerge_agents  AS a ON a.id = h.agent_id
WHERE e.error_text LIKE N'%Resolver%'
ORDER BY e.time DESC;

See which articles use a stored procedure resolver, in the publication database:

SELECT p.name AS Publication, a.name AS Article,
       a.article_resolver, a.resolver_info AS ResolverProc
FROM dbo.sysmergearticles AS a
JOIN dbo.sysmergepublications AS p ON p.pubid = a.pubid
WHERE a.article_resolver LIKE N'%Stored Procedure%';

To catch the failing row, trace rpc_completed and sql_batch_completed on the publisher and filter on the resolver proc name. The rowguid it was called with is the row to look at. Then run the proc by hand with that value.

How to fix it

Unblock the sync first, then fix the proc, then switch it back. The finding includes the exact commands for each article.

1. Switch the article to the default resolver. This needs no new snapshot and no reinitialization, and the merge agent can move on at once.

EXEC sp_changemergearticle
     @publication = N'MyPublication', @article = N'MyArticle',
     @property = N'article_resolver', @value = NULL,
     @force_invalidate_snapshot = 0, @force_reinit_subscription = 0;

2. Fix the resolver proc. For the usual causes:
   • Return the winning row directly with SELECT ... WHERE rowguid = @rowguid, instead of copying it through a temp table.
   • Use an explicit column list in published table column order, not SELECT *.
   • Handle NULLs and a missing row on either side, and fall back to the side that has it.
   • Make sure it returns exactly one row with the same rowguid it was passed.
   • Use the full signature: @tableowner, @tablename, @rowguid, @subscriber, @subscriber_db, @log_conflict OUTPUT, @conflict_message OUTPUT, and @destowner.
3. Switch the article back, setting both properties:

EXEC sp_changemergearticle
     @publication = N'MyPublication', @article = N'MyArticle',
     @property = N'article_resolver',
     @value = N'Microsoft SQLServer Stored Procedure Resolver',
     @force_invalidate_snapshot = 0, @force_reinit_subscription = 0;

EXEC sp_changemergearticle
     @publication = N'MyPublication', @article = N'MyArticle',
     @property = N'resolver_info', @value = N'dbo.MyResolverProc',
     @force_invalidate_snapshot = 0, @force_reinit_subscription = 0;

4. If the proc reads the subscriber through @subscriber, create the linked server and map the login the merge agent uses, and turn data access on:

EXEC sp_serveroption N'SubscriberServer', 'data access', 'true';

Run these commands in the publication database. The check builds the exact text for each article, so copy them from the finding rather than from here.

While the default resolver is in use, conflicts are settled by default rules and not by your proc. The RESOLVER_INACTIVE finding is there so a temporary workaround does not become permanent by accident.

How long it takes

About two hours for one resolver, including reading the error, reproducing it with the failing rowguid, fixing the proc and watching a clean sync. Unblocking the agent on its own takes minutes.

Related reports

Related checks

Frequently asked questions

Why is a finding reported when the agent has recovered? It is not. A recovered error, or one where the article is already on the default resolver, is checked and left out. Only errors that are still failing, possibly fixed, or not verifiable are reported.

Why is a code finding reported when nothing has failed? Those findings are pattern based. They flag code that is known to fail in particular conditions, such as a NULL or a missing row. They are risk, not proof, and a Low finding is worth fixing at the next change rather than today.

Does switching to the default resolver lose data? The default resolver settles each conflict by its own rules, which can pick a different winner than your proc would. Switch back as soon as the proc is fixed, and expect the conflicts that arrived in between to have been decided by the default.

Why does the error only appear on the distributor? The agent history is stored in the distribution database. The code checks run on the publisher because that is where the resolver procs live. If this instance is both, you get both kinds.