Conflicts from nowhere

I have two tables that are getting conflicts between the subscriber and the publisher, however I am pretty sure we only update these tables at the subscriber.

I have column level tracking turned on.

The tables both have Nonoverlapping, single subscription (3) set for partition options. I wonder does this do any maintenance to the tables?

I have put in place some triggers to audit what makes changes to the data, but won't know until tomorrow, so if there is something that anyone knows about that might help, please let me know.

Thanks

Hi,

Could you return the contents of MSmerge_conflicts_info and MSmerge_conflict_publication_article table for the article where the conflict occurred? Also, there are probably some information about conflict in merge agent history. Do you have some primary key, foreign key constraint on publisher only? That will cause conflict if the subscriber row violated the constraint and failed to be applied on publisher.

Hope it helps.

Wanwen

|||some more questions as well - what does the filtered column look like, and are you updating the filtered column?|||The filtered column is a bit field and is only updated at the server - with the partition options set to 3 (and use partition groups set to true) you cannot change that value at the subscriber. The field gets read only permissions in the database.|||The triggers I put in place to audit the updates to the table I am conerned with revealed that there were no updates occuring that were not expected.

So, what happens is the data gets inserted via a biztalk process. The data then gets synchronised to a mobile device where an update occurs.

Upon synchronisation, the data updates going back are met with a conflct - wwhich must surely be all but impossible if nothing has touched the data on the server.

To make matters worse, this doesn't occur for every updated record only for some of them. It also does not occur to other tables set up in the same way.

Something is very fishy about this..|||

Hi,

Could you please give more information so we can try to understand and repro the problem?

Could you return the contents of MSmerge_conflicts_info and MSmerge_conflict_publication_article table for the article where the conflict occurred? Also, there are probably some information about conflict in merge agent history. Do you have some primary key, foreign key constraint on publisher only? That will cause conflict if the subscriber row violated the constraint and failed to be applied on publisher.

Do you have join filters defined in your publication? when you do update, do you update the rows on both base article and join article? what is your subset_filter and join filter looks like?

Thanks

Wanwen

|||OK, more information as requested (should have done this when you asked the first time).

Two articles in my database that I am getting these unexplained
conflicts for.
job_travel
meter

CREATE TABLE [dbo].[job_travel](
[jot_id] [uniqueidentifier] NOT NULL CONSTRAINT [df_jot_id] DEFAULT (newid()),
[jot_guid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [df_jot_guid] DEFAULT (newsequentialid()),
[jot_job_id] [uniqueidentifier] NOT NULL,
[jot_travel_start] [datetime] NULL,
[jot_travel_stop] [datetime] NULL,
[jot_travel_duration] [int] NULL,
[jot_is_active] [bit] NOT NULL CONSTRAINT [df_jot_is_active] DEFAULT ((1)),
CONSTRAINT [pk_jot_id] PRIMARY KEY CLUSTERED
(
[jot_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF)
)

CREATE TABLE [dbo].[meter](
[met_id] [uniqueidentifier] NOT NULL CONSTRAINT [df_met_id] DEFAULT (newid()),
[met_guid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [df_met_guid] DEFAULT (newsequentialid()),
[met_job_id] [uniqueidentifier] NOT NULL,
[met_reading] [int] NULL,
[met_number] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[met_is_active] [bit] NOT NULL CONSTRAINT [df_met_is_active] DEFAULT ((1)),
CONSTRAINT [pk_met_id] PRIMARY KEY CLUSTERED
(
[met_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF)
)

Each article hangs from the job table which in turn hangs from the sys_user table

joins to get there are as follows

-- Select the technician information
SELECT <published_columns> FROM [dbo].[sys_user] WHERE syu_code = SUSER_SNAME() AND syu_is_active = 1

-- select the jobs for the technician
SELECT <published_columns> FROM [dbo].[sys_user] INNER JOIN [dbo].[job] ON sys_user.syu_id = job.job_syu_id AND job.job_is_active = 1

-- select the job travel records for each job
SELECT <published_columns> FROM [dbo].[job] INNER JOIN [dbo].[job_travel] ON job.job_id = job_travel.jot_job_id AND job_travel.jot_is_active = 1

-- select the meter records for each job
SELECT <published_columns> FROM [dbo].[job] INNER JOIN [dbo].[meter] ON job.job_id = meter.met_job_id AND meter.met_is_active = 1

MSmerge conflict info for job_travel (3 useful columns - let me know if you need more)
conflict_type reason_code reason_text
2 2 The same column(s) of the same row was updated at both '25a - 1193b9285dee' and 'mas.canon_map'. The resolver chose the update from '25a - 1193b9285dee' as the winner.

A typical job_travel record from the conflict table

jot_id jot_guid jot_job_id jot_travel_start jot_travel_stop jot_travel_duration jot_is_active origin_datasource_id
5ED06CA2-C513-46EE-BA52-A7CD7327D195 A8243A8C-0316-DB11-807E-00145E2A163D 95BFBB97-694E-4526-8FBE-7D31CB35251C 2006-07-18 14:18:00.000 NULL NULL 1 1CEA745B-FAF4-4772-A5D1-3E644DD40874

MSmerge conflict info for meter (3 useful columns - let me know if you need more)

conflict_type reason_code reason_text
2 2 The same column(s) of the same row was updated at both '73w - e17b9ff3c3eb' and 'mas.canon_map'. The resolver chose the update from '73w - e17b9ff3c3eb' as the winner.

A typical meter record from the conflict table
met_id met_guid met_job_id met_reading met_number met_is_active origin_datasource_id
127380E3-E1AF-49BD-8B6C-FB5338287CDD F2D5A09B-0C15-DB11-807E-00145E2A163D 7594C31D-1805-46B8-B2A8-F1EDB46E8C06 NULL 1 1 1CEA745B-FAF4-4772-A5D1-3E644DD40874

<quote>
Also, there are probably some information about conflict in merge agent history
</quote>

Not sure what you're looking for here?

<quote>

Do you have some primary key, foreign key constraint on publisher only?

</quote>

No, constraints are consistent at both publisher and subscriber.

<quote>

Do

you have join filters defined in your publication?
</quote>

Yes, as shown above

<quote>

when you do update,

do you update the rows on both base article and join article?
</quote>

potentially.

Sometimes we will and sometimes we won't. Information on a meter or a job_travel record may change without any change to the job data. A meter or job_travel change does not force a change to the job record.

To re-iterate to data flow.

A record gets create at the publisher.
Synchronisation ensures the subscriber has access to the record.
A data update occurs at the subscriber. (job_travel.jot_travel_stop) (meter.met_reading)
NO data updates occur at the publisher.
Synchronsation ensures the change goes from the subscriber to the publisher.
On some occasions - not all - a conflict occurs.
The custom resolver (subscriber always wins) kicks in and resolves the data - EVENTUALLY. There is a definite delay in this process causing the data extract that has been triggered to extract unresolved data (publisher data before the update)

Hopefully this is sufficient information for you to continue with. Ask for more and I'll see what I can provide.