sql server replication: Hi all,

In a nuttshell:
- SQL Server 2000 (sp4) with approx. 10 subscribers (MSDE SP4)
- Merge replication, subscription expiration set to 30 days
- Merge replication set to continuously
- Agent (and SQL) run with a domain-account that has admin-rights on
the server and on the local boxes.
- Subscription created with PULL (using EM on the server) and are
anonymus
- Publication and subscriptions have been setup about 14 days ago

Out of all the subscribers, there is one subscription that is acting
strange: it will synchronise changes FROM the server correctly but it
will not synchronise the local changes TO the server.

- Agents report "No data neede to be merged"
- After server changes, this changes to the number of
inserts/updates/etc. This is almost instantly after a database change
is made on another computer (or on the server).
- SQL Error logs do not provide any clues (no errors)
- Manually starting the agents makes no difference
- Rebooting client makes no difference
- Manually starting the agent on the subscription machine makes no
difference

I'm a bit puzzled here. The only solution I see at the moment is to
delete the current subscription and setup a new one. Obviously, that
particular user is not very happy with that. Not only will he lose all
changes but he (and the local admin) are worried that this 'problem'
occurs again or on other machines...

Any clues on where to look for this ?

Thx, Ferry

The first place I'd look is in the agent's job to see if there is
an -EXCHANGETYPE parameter used there in the script arguments for the second
step - that would explain it.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

From Paul Ibison :
[quoted text, click to view]

Thanks Paul, just double-checked. Although there is only one step, it
looks good to me (since the Exchangetype parameter isn't there (on none
of the subscribers), It should default to 3 (according to
replicationanswers.com :-) ):
-Publisher [VOYAGER] -PublisherDB [Alca168] -Publication
[Alca168_Subscription] -Subscriber [DEEPSPACE9] -SubscriberDB
[Alca168_Subscription] -SubscriptionType 2 -SubscriberSecurityMode 0
-SubscriberLogin [sa] -SubscriberEncryptedPassword [<unprintable chars
removed>] -Distributor [VOYAGER] -Continuous

Ah yes, good site that :)

For the troubleshooting, my next idea would be to check that your merge
triggers are firing. Find a row that doesn't exist in msmerge_contents using
the guid and update it to see if the row arrives there. What I'm thinking is
that the changes that have been made on your subscriber might have been done
via a fast data load from DTS or bulk insert, which don't fire the merge
triggers by default. After the manual edit, if the record reaches
msmerge_contents and synchronizes, you've founsd the issue.

Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

From Paul Ibison :
[quoted text, click to view]

I'll look into that to be sure, but AFAK, there is only data entered
using a front-end application (Access and ADO). And other users (same
Axs app) don't have the problem.

The user just removed his laptop from the network, no way I can look
into this again before Monday... And my guess is they're going to kill
the subscription, pull a new one and do some tests to see if it works.

Thanks for the suggestions,

Ferry

Paul (and others that might benefit from this),

While investigating the problem on machine a, the exact same problem
occured on machine b. Panic at the customers site...

Since the enduser didn't want to be without the laptop, they removed
the subscription and setup a new one. This time, they made the
subscription 'named' (the other ones were 'anonymous').

Surprise, since then, no issues anymore on these 2 machines. So,
they're going to drop all anonymous subscriptions and give those users
named ones.

Ferry

Ferry - thanks for the update. I can't help thinking that the solution is a
red-herring and the problem probably isn't related to anonymous or named
subscriptions. I realise that you probably won't want to muck around at a
customer's site, but if you ever have a chance, perhaps in a test
environment, we could test my theory by setting up another anonymous
subscriber and testing it. I think that the reinitialization process is
removing something else that is the real issue. I wish I'd asked you to
script out the problem publications and we'd probably find the answer there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)