Conflicts in Updateable Subscriptions

I've been experiencing conflicts in my replication system that I can't seem to get my head around. The following is the scenario:

3 sqlservers, all running sql server 2005. Server B is the publisher and Server A and Server C (64 bit) are subscribers. The Queue Reader Agent runs on the publisher. I set up transactional repl with updateable subscriptions with the default conflict resolution policy of 'Publisher wins'.

There are 2 kinds of processes: 1. Nightly batch updates and 2. Daytime updates by real clients. The Nightly batch updates runs an on the publisher, which is B. Batch updates are massive updates and running it on the publisher makes sense and it works like a charm. Online updates are made on the subscriber 'C'. This subscriber is set to Queued update mode, and everyday I see a significant number of transactions that are detected as conflicts and the Publisher wins. As a result the changes made on Server C are getting lost. I have verified that no user/client is logged into Server B to do any updates. Users complain that their updates are lost. This is the most puzzling and frustrating bit. I don't see how a conflict can happen if nobody is updating data on the Publisher during the day. SQL Updates on Server C are getting rolled back on a conflict detection because the "Publisher wins", and SQL Inserts on server C are getting deleted because they don't exist on the publisher. Now, how can a insert done on the subscriber be marked as a conflict. There is no row on the publisher to compare the unique guid with, how can it be a conflict?

And the Queue Reader Agent crashes every 3-4 days. No useful information except it creates a dump file for which users have no tools to read it.

Has anyone seen this behavior ? Or is there a known bug in the QueueReader Agent?

My users are losing faith in the replication system and so am I.

Thanks for your time,

-chiraj.

Hi,

Have you use the replication conflict viewer to see who is the winner of the conflict and what type is the winner transactions? It may be able to help you figure out where the conflict is from.

Peng

|||

Hi Peng,

Thank you for responding.

I looked at the conflict viewer, and the conflict winner is always the Publisher, and the conflct type is "Update conflict" WHen I click on any the rows in the conflict list, I get a popup error "Incorrect syntax near the keyword 'from', Error 21465, severity 16, state 1 raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage. (Microsoft SQL Server,Error: 156)". It pops up twice and when I get rid of them, the bottom pane of the viewer has the info: Queued update conflict. Publisher wins. Updating Subscriber. There are 3 columns: Column name, Conflict winner and Conflict loser. The Conflict winner column says "this row no longer exists in 'schema.tablename'". The conflict loser column has values for the table columns. I don't know the contents of the winner, maybe because of the earlier error with the popup message.

Any ideas?

Thanks.

|||

I did some digging and the popup error comes from sys.sp_MSgettrancftsrcrow procedure. Towards the end of the procedure, you have :

if (@.is_debug = 0)

begin

select @.cmd = N'select cmdtext from MSsrcrow_codetext order by step'

,@.dbname = db_name()

exec @.retcode = sys.xp_execresultset @.cmd, @.dbname

if (@.@.error != 0 or @.retcode != 0)

begin

raiserror(21465, 16, 1, 'xp_execresultset')

return (1)

end

end

else

I wonder why the message for #21465 is not installed.

I basically want to see and compare all the columns ( except timestamp ) from the subscriber and the publisher and see why there is a conflict.

I made sure the data was in sync this morning and then I start seeing conflicts during the day although no one is logged into the publisher. Beats me. Is there any trace flag I can turn on for replication to see what's going on?

-chiraj.