Conflicts consistently occur on merge

Hi,

I was hoping a sharp eye may be able to pick up on what I am doing wrong here, I am i must be making a comon mistake. If necessary I am happy to create and post a sample if necessary.

Problem:

The publication parent table has a filter (defined below) and also has a join filter(defined below) to a child table. When changes are made to the parent and child table and synchronised a conflict is raised. For the child table for some reason the merge agent is saying an explicit update occured at the publisher but the values at the publisher have not changed at all and no sql has been executed to update the publisher rows. This in effect is stopping the changes to the child table being applied until i manually apply them in the conflict resolver. The strange thing is the conflict should not occur in the first place.

Perhaps my SQL server 2005 version? 9.00.2050.00 SP1 Standard Edition

Environment:

SQL Server 2005 - 9.00.2050.00 SP1 Standard Edition

SQL Server Mobile

Detail:

FKs are

FK: Job.JobScheduleID --> JobSchedule.ID

FK: JobDetail.JobID --> Job.ID

All three tables have int based IDs that are auto increment.

Publication Articles:

JobSchedule is download only

Job is Bidirectional, identity range management is MANUAL (only updates occur on this table)

JobDetail is Bidirectional, identity range management is MANUAL (only updates occur on this table)

Filters are of the following form:

Filter Job:

SELECT <published_columns> FROM [dbo].[Job] WHERE convert(nchar,[companyID])=Host_Name() AND [JobCompletedDate] IS NULL AND

( [JobScheduleID] in (SELECT distinct ID from JobSchedule where GETDATE() BETWEEN [JobSchedule].[start] AND [JobSchedule].[end]) )

Join Filter Job --> JobDetail

SELECT <published_columns> FROM [dbo].[Job] INNER JOIN [dbo].[JobDetail] ON [Job].[ID] = [JobDetail].[JobID]

Now the first thing to note with respect to using GetDate() i have read http://msdn2.microsoft.com/en-us/library/ms365153.aspx so i thought that i would remove that portion of the Filter on the Job table just to see what happens.

SELECT <published_columns> FROM [dbo].[Job] WHERE convert(nchar,[companyID])=Host_Name() AND [JobCompletedDate] IS NULL

This still did not resolve the issue. I then Remove the AND [JobCompletedDate] IS NULL and it then started working fine (cool) but of course no longer satisfys the rule i want to create (seriously uncool ).

Any ideas out there ?

Much appreciated,

pdns

Could you post the message from the conflict resolver.

Also did you set your relationships to be not for replication?

Martin

|||Quick thought - merge replication will perform dummy updates on your children tables when an update on the parent table results in a partition movement, so if your sql is updating the partition columns on the parent table this could be the cause of the child updates.|||

Hi Martin,

The conflict resolve message are as follows:

16/08/2007 12:00:00 AM "submit loser" "JOBDB" "JobPub" "JobDetail" JobPub - 25891a8ed36f 38c09202-0d4b-dc11-ae51-00c09f31ae1d 2(Column update conflict) The same column(s) of the same row was updated at both 'sql52.JOBDB' and 'JobPub - 25891a8ed36f'. The resolver chose the update from 'sql52.JobDB' as the winner. set DATEFORMAT mdy UPDATE [dbo].[JobDetail] SET [dbo].[JobDetail].[bit_sitevisited] MSmerge_conflict_JobPub_JobDetail].[bit_sitevisited] FROM [dbo].[JobDetail],[MSmerge_conflict_JobPub_JobDetail] WHERE [dbo].[JobDetail].[rowguid]='38c09202-0d4b-dc11-ae51-00c09f31ae1d' AND [dbo].[JobDetail].[rowguid]=[MSmerge_conflict_JobPub_JobDetail].[rowguid]

The Only the ID columns on the Job and JobDetail are set to NOT For Replication, are you talking about the FK constraints being set to Not For Replication.

Thnx,

pdns

|||

Hi roamingsim,

The filter for the parent table, namely Job, uses [JobCompletedDate] IS NULL in the where clause. On the device the JobCompletedDate is updated when a job is completed. Is there any way i can determine if this is causing the child updates ? and if it is what would be the normal way of avoiding this?

Thnx,

pdns

|||I believe it almost certainly will be causing dummy updates as setting the JobCompletedDate to not null effectively excludes the parent record from the partition - replication then needs to perform dummy updates to the children records so that they to are re-evaluated and removed from the partition. I don't think you want to stop this, as I assume you want these children records removed from the subscriber, so the question is more how you handle the conflicts if this is indeed the cause. Sorry I'm less familiar with conflict resolution.

If you want to check that these dummy updates you can perform the following simple test (no doubt there's better ways to find the pending changes involving querying the msmerge tables, but this test should give you some idea):

- Sync the subscriber to make sure it's up-to-date. (sync this a couple of times and you should see in the replication monitor that there are no updates being sent to or from the subscriber.
- Update one record on the parent table, setting the JobCompletedDate to something other than null
- sync again and check the details in the replication monitor. You should see one record updated or deleted on the parent table and one or more records being updated or deleted on the children tables.

This is probably not the source of your problem, but it may answer your original question about where these updates are coming from.