Merge filter inconsistant

We are doing merge replication between a central server and multiple
anonymous clients. We use a filter on one table and joins to related
tables to determine what records to merge. There are two parts to the
filter, one is user, which merges only records assigned to the user
that is replicating. The second is a merge code, which is set to 0 or
1 and basically serves as a completion code: when the user is done
with the record, it is set to complete and is no longer included in
the merge.
The problem has to do with changes to the merge code. As long as it
is set to 1, replication occurs smoothly. After it is set to 0 and
merged so that client and server are 0, changes made to the client
records continue to be merged with the server until a change is made
to the table on the server. So, if there are two tables A & B, when
table A is changed on the server, local changes to A will no longer be
merged but local changes to B will continue to merge until a change is
made to B on the server. Since A & B are related tables, this is a
serious synchronization issue! It is as though a server change has to
be made to each table to force re-evaluation of the filter criteria
for that table, strange.
Here are the details of our setup:
There are 3 tables for which merge replication is defined on SQL
Server 2000 on NT:
1. WI_ASGN
wi_asgn_guid
wi_guid (fk to work_item constraint)
asgn_to_user_id
dnld_merg_cd
2. WORK_ITEM
wi_guid
office_id
3. WI_DETAIL
dtl_guid
wi_guid (fk to work_item constraint)
start_date
A row filter is defined on the WI_ASGN table: WHERE
[WI_ASGN].[WI_GUID] IN (SELECT WI_GUID FROM
[dbo].[WI_ASGN] WHERE (ASGN_TO_USER_ID =
dbo.fGetCurrentUserID()) and (DNLD_MERG_CD = 1))
fGetCurrentUserID is a user defined function which returns a char(8)
user_id.
Join filters are used to filter WORK_ITEM:
SELECT <published_columns> FROM [dbo].[WI_ASGN] INNER JOIN
[dbo].[WORK_ITEM] ON [WI_ASGN].[WI_GUID] =
[WORK_ITEM].[WI_GUID]
and WI_DETAIL:
SELECT <published_columns> FROM [dbo].[WI_ASGN] INNER JOIN
[dbo].[WI_DETAIL] ON [WI_ASGN].[WI_GUID] =
[WI_DETAIL].[WI_GUID]
We begin with DNLD_MERG_CD = 1 and merge replication running smoothly.
After the user has completed working on the assignment, the data no
longer should no longer replicate even if the user makes chagnes. We
reset the DNLD_MERG_CD to 0 so that the replication filter condition
would not be met.
After doing this, the problem occurs. The following steps were
executed to test the replication:
Action:
DNLD_MERG_CD is set to 0.
Merge is executed.
Result:
Merge code is 0 on both databases (expected)
Action:
WORK_ITEM and WI_DETAIL are updated on the Local database.
Merge is executed
Result:
Local changes are replicated to the Central database. (not expected
since merge filter condition is not met)
Action:
WORK_ITEM is updated on the Central database.
Merge is executed
Result:
The Central change is not replicated to the Local database. (expected)
Action:
WORK_ITEM and WI_DETAIL are updated on the Local database.
Merge is executed
Result:
Local change to WORK_ITEM is not replicated (expected, but different
result then the first local change)
Local change to WI_DETAIL is replicated to Central database (not
expected since merge filter condition is not met)
Action:
WI_DETAIL is updated on the Central database.
Merge is executed
Result:
The Central change is not replicated to the Local database. (expected)
Action:
WORK_ITEM and WI_DETAIL are updated on the Local database.
Merge is executed
Result:
Local changes ar not replicated (expected)
Why do the changes to the Local database tables continue to replicate
until the corresponding Central database table is modified?
Sorry for the length of this post, but it seemed necessary. Thanks
for your help.
Kees VanTilburg
VanTilburg Enterprises
Hi Kees,
There is something strange happening here.
Firstly, if a row meets your subset filter requirements it will be
replicated to the subscriber from the publisher. Then, subscriber DML on
that row will be replicated back to the publisher.
If, however, your subset filter excludes the row then the row will not
replicate to the subscriber in the first place. Now, in your case, the
row does at first match the filter's requirements so it is replicated to
the subscriber. But when you change the row data so that the row does
not match the filter anymore (we call that "moving out of partition")
the row at the subscriber should be deleted since it no longer belongs
in the subscriber's partition. Because your row (and all its dependent
rows) does not get removed from the subscriber, it leads me to believe
that something could be wrong with the subset filterclause.
Looking at your subset filterclause, the only thing that struck me as a
bit strange was the unnecessary double select. Could you try use the
following instead:
change:
WHERE [WI_ASGN].[WI_GUID] IN (SELECT WI_GUID FROM [dbo].[WI_ASGN] WHERE
(ASGN_TO_USER_ID = dbo.fGetCurrentUserID()) and (DNLD_MERG_CD = 1))
to:
WHERE (ASGN_TO_USER_ID = dbo.fGetCurrentUserID()) and (DNLD_MERG_CD = 1)
There shouldn't be a reason to do the first IN selection since the
second WHERE clause is all you're interested in.
Also, you might try (for testing sake) remove the first requirement and
simply state WHERE DNLD_MERG_CD = 1 as your subset filterclause. See if
this works and then add fGetCurrentUserID() back again.
Hope this helps,
Reinout Hillmann
SQL Server Product Unit
This posting is provided "AS IS" with no warranties, and confers no rights.
kjvt wrote:
> We are doing merge replication between a central server and multiple
> anonymous clients. We use a filter on one table and joins to related
> tables to determine what records to merge. There are two parts to the
> filter, one is user, which merges only records assigned to the user
> that is replicating. The second is a merge code, which is set to 0 or
> 1 and basically serves as a completion code: when the user is done
> with the record, it is set to complete and is no longer included in
> the merge.
> The problem has to do with changes to the merge code. As long as it
> is set to 1, replication occurs smoothly. After it is set to 0 and
> merged so that client and server are 0, changes made to the client
> records continue to be merged with the server until a change is made
> to the table on the server. So, if there are two tables A & B, when
> table A is changed on the server, local changes to A will no longer be
> merged but local changes to B will continue to merge until a change is
> made to B on the server. Since A & B are related tables, this is a
> serious synchronization issue! It is as though a server change has to
> be made to each table to force re-evaluation of the filter criteria
> for that table, strange.
> Here are the details of our setup:
> There are 3 tables for which merge replication is defined on SQL
> Server 2000 on NT:
> 1. WI_ASGN
> wi_asgn_guid
> wi_guid (fk to work_item constraint)
> asgn_to_user_id
> dnld_merg_cd
> 2. WORK_ITEM
> wi_guid
> office_id
> 3. WI_DETAIL
> dtl_guid
> wi_guid (fk to work_item constraint)
> start_date
> A row filter is defined on the WI_ASGN table: WHERE
> [WI_ASGN].[WI_GUID] IN (SELECT WI_GUID FROM
> [dbo].[WI_ASGN] WHERE (ASGN_TO_USER_ID =
> dbo.fGetCurrentUserID()) and (DNLD_MERG_CD = 1))
> fGetCurrentUserID is a user defined function which returns a char(8)
> user_id.
> Join filters are used to filter WORK_ITEM:
> SELECT <published_columns> FROM [dbo].[WI_ASGN] INNER JOIN
> [dbo].[WORK_ITEM] ON [WI_ASGN].[WI_GUID] =
> [WORK_ITEM].[WI_GUID]
> and WI_DETAIL:
> SELECT <published_columns> FROM [dbo].[WI_ASGN] INNER JOIN
> [dbo].[WI_DETAIL] ON [WI_ASGN].[WI_GUID] =
> [WI_DETAIL].[WI_GUID]
> We begin with DNLD_MERG_CD = 1 and merge replication running smoothly.
> After the user has completed working on the assignment, the data no
> longer should no longer replicate even if the user makes chagnes. We
> reset the DNLD_MERG_CD to 0 so that the replication filter condition
> would not be met.
> After doing this, the problem occurs. The following steps were
> executed to test the replication:
> Action:
> DNLD_MERG_CD is set to 0.
> Merge is executed.
> Result:
> Merge code is 0 on both databases (expected)
> Action:
> WORK_ITEM and WI_DETAIL are updated on the Local database.
> Merge is executed
> Result:
> Local changes are replicated to the Central database. (not expected
> since merge filter condition is not met)
> Action:
> WORK_ITEM is updated on the Central database.
> Merge is executed
> Result:
> The Central change is not replicated to the Local database. (expected)
> Action:
> WORK_ITEM and WI_DETAIL are updated on the Local database.
> Merge is executed
> Result:
> Local change to WORK_ITEM is not replicated (expected, but different
> result then the first local change)
> Local change to WI_DETAIL is replicated to Central database (not
> expected since merge filter condition is not met)
> Action:
> WI_DETAIL is updated on the Central database.
> Merge is executed
> Result:
> The Central change is not replicated to the Local database. (expected)
> Action:
> WORK_ITEM and WI_DETAIL are updated on the Local database.
> Merge is executed
> Result:
> Local changes ar not replicated (expected)
> Why do the changes to the Local database tables continue to replicate
> until the corresponding Central database table is modified?
> Sorry for the length of this post, but it seemed necessary. Thanks
> for your help.
> Kees VanTilburg
> VanTilburg Enterprises
>
|||I assume that your subscribers are runnig sql server ce.
Take a look at
http://support.microsoft.com/default...b;EN-US;315758
http://support.microsoft.com/default...b;EN-US;287651
http://support.microsoft.com/default...b;EN-US;827914
Marius
|||Thank you for your reply. No, the subscribers are running MSDE.
Kees
On Thu, 13 May 2004 11:25:32 +0300, "Marius Bucur"
<marius.bucur@.optima.ro.nospan> wrote:

>I assume that your subscribers are runnig sql server ce.
>Take a look at
>http://support.microsoft.com/default...b;EN-US;315758
>http://support.microsoft.com/default...b;EN-US;287651
>http://support.microsoft.com/default...b;EN-US;827914
>Marius
>
|||Reinout,
Thank you for your reply. Please see my comments below.
Kees
On Wed, 12 May 2004 18:02:06 -0700, "Reinout Hillmann [MS]"
<reinouth@.online.microsoft.com> wrote:

>Hi Kees,
>There is something strange happening here.
>Firstly, if a row meets your subset filter requirements it will be
>replicated to the subscriber from the publisher. Then, subscriber DML on
>that row will be replicated back to the publisher.
>If, however, your subset filter excludes the row then the row will not
>replicate to the subscriber in the first place. Now, in your case, the
>row does at first match the filter's requirements so it is replicated to
>the subscriber. But when you change the row data so that the row does
>not match the filter anymore (we call that "moving out of partition")
>the row at the subscriber should be deleted since it no longer belongs
>in the subscriber's partition. Because your row (and all its dependent
>rows) does not get removed from the subscriber, it leads me to believe
>that something could be wrong with the subset filterclause.
>Looking at your subset filterclause, the only thing that struck me as a
>bit strange was the unnecessary double select. Could you try use the
>following instead:
>change:
>WHERE [WI_ASGN].[WI_GUID] IN (SELECT WI_GUID FROM [dbo].[WI_ASGN] WHERE
>(ASGN_TO_USER_ID = dbo.fGetCurrentUserID()) and (DNLD_MERG_CD = 1))
>to:
>WHERE (ASGN_TO_USER_ID = dbo.fGetCurrentUserID()) and (DNLD_MERG_CD = 1)
>There shouldn't be a reason to do the first IN selection since the
>second WHERE clause is all you're interested in.
There's another layer of complexity that necessitates the subquery.
However, I think it would be better to filter on the Work_Item table
as follows:
WHERE [Work_Item].[WI_GUID] IN (SELECT WI_GUID FROM [dbo].[WI_ASGN]
WHERE (ASGN_TO_USER_ID = dbo.fGetCurrentUserID()) and (DNLD_MERG_CD =
1))
Work item has a one-many relationship with wi_asgn (there may be many
employees assigned to a single work item). If I am assigned to a work
item, I need to replicate all the assignment records for the work item
(including other employees' assignment records). But I don't want any
assignments for work items that I'm not assigned to. So for example:
wi_guidasgn_to_user_iddnld_merg_cd
1me1
1john1
1jane1
2john1
3jane1
If I merge, then I need to replicate the first three rows. If i
filter on Work_item as specified above, then join wi_asgn to work_item
on wi_guid, that should return the proper rows. This is different
from what I was doing prior (the filter was on wi_asgn, which was then
joined to work_item), so I'll test this and report back.

>Also, you might try (for testing sake) remove the first requirement and
>simply state WHERE DNLD_MERG_CD = 1 as your subset filterclause. See if
>this works and then add fGetCurrentUserID() back again.
I'll also try this simpler test and report back.

>
>Hope this helps,
>Reinout Hillmann
>SQL Server Product Unit
|||Hi Kees,
I am just checking on your progress regarding you said you will show us
your report back. I wonder how the testing is going. If you encounter any
difficulty, please do not hesitate to let me know. Please post here and let
me know the status of your issue. Without your further information, it's
very hard for me to continue with the troubleshooting.
Looking forward to hearing from you soon.
Sincerely yours,
Michael Cheng
Microsoft Online Support
************************************************** *********
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
|||Michael,
Based on further testing, it appears to be the subquery in the filter
clause that causes the problem. Please see below for updates on my
test results that led to this conclusion.
How can I restructure the filter to eliminate the problem while still
meeting my requirement?
Thank you.
Kees
On Thu, 13 May 2004 17:09:59 -0500, kjvt <kjvt@.newsgroups.nospam>
wrote:

>Reinout,
>Thank you for your reply. Please see my comments below.
>Kees
>
>On Wed, 12 May 2004 18:02:06 -0700, "Reinout Hillmann [MS]"
><reinouth@.online.microsoft.com> wrote:
>
>There's another layer of complexity that necessitates the subquery.
>However, I think it would be better to filter on the Work_Item table
>as follows:
>WHERE [Work_Item].[WI_GUID] IN (SELECT WI_GUID FROM [dbo].[WI_ASGN]
>WHERE (ASGN_TO_USER_ID = dbo.fGetCurrentUserID()) and (DNLD_MERG_CD =
>1))
>Work item has a one-many relationship with wi_asgn (there may be many
>employees assigned to a single work item). If I am assigned to a work
>item, I need to replicate all the assignment records for the work item
>(including other employees' assignment records). But I don't want any
>assignments for work items that I'm not assigned to. So for example:
>wi_guidasgn_to_user_iddnld_merg_cd
>1me1
>1john1
>1jane1
>2john1
>3jane1
>If I merge, then I need to replicate the first three rows. If i
>filter on Work_item as specified above, then join wi_asgn to work_item
>on wi_guid, that should return the proper rows. This is different
>from what I was doing prior (the filter was on wi_asgn, which was then
>joined to work_item), so I'll test this and report back.
>
I retested after moving the filter criteria to the work_item table
WHERE [Work_Item].[WI_GUID] IN (SELECT WI_GUID FROM [dbo].[WI_ASGN]
WHERE (ASGN_TO_USER_ID = dbo.fGetCurrentUserID()) and (DNLD_MERG_CD =
1))
and received the same results (see my note describing the original
problem).

>I'll also try this simpler test and report back.
A simple filter clause (without the subquery)
WHERE (ASGN_TO_USER_ID = dbo.fGetCurrentUserID()) and (DNLD_MERG_CD =
1)
works correctly. When the dnld_merge_cd is set to 0 and a merge is
executed, the row is deleted from the local (subscriber) database.
(Thus my conclusion that the subquery is causing the problem.)
However, this approach does not return all the records that I need.
[vbcol=seagreen]
|||Hi Kees,
Thanks for your update.
From your descriptions, I understood that
1.
WHERE (ASGN_TO_USER_ID = dbo.fGetCurrentUserID()) and (DNLD_MERG_CD =1)
the WHERE clause works well, why didn't you use this clause only?
2.
WHERE [Work_Item].[WI_GUID]
IN
(SELECT WI_GUID
FROM [dbo].[WI_ASGN]
WHERE (ASGN_TO_USER_ID = dbo.fGetCurrentUserID()) and (DNLD_MERG_CD =1))
By using WHERE clauses above, could you make the right result back in Query
Analyzer? Could you make all other clause and condition right back from
Query Analyzer? I am not sure what dbo.fGetCurrentUserID() send back, but
based on my testing, the above could work well.
Could you make it without dbo.fGetCurrentUserID()?
Would you please send me some sample data that could reproduce the problem?
Could you ensure dbo.fGetCurrentUserID() send the right result and
condition for WHERE clause?
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know.
Sincerely yours,
Michael Cheng
Microsoft Online Support
************************************************** *********
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
|||Hi Kees,
I checked your replication logic, couldn't figure out why the replication
behaves like that.
So I setup a merge replication to test, using one table:
1. Create the merge table:
create table T3 (c1 int, c2 int, c3 char(8), c4 int)
insert T3 values (1, 1, 'one', 0)
2. Checked the filter statement:
Select * from T3 where c1 in (select c1 from T3 where c3 = 'one' and c4 =1)
=> 0 row returned
Select * from T3 where c3 = 'one' and c4 =1 => 0 row returned.
3. Setup a merge replication for this table (T3) with row filter:
SELECT <published_columns> FROM [dbo].[T3] WHERE c1 in (select c1 from T3
where c3 = 'one' and c4 = 1)
4. Run Snapshot and merge agents. Checked the subscriber table, no row was
copied, This is as expected, since no row satisfies the filter condition..
5. updated publisher:
update T3 set c4=1
6. Run merge agent, one row went to the sub, as expected. Now both pub and
sub has one row with c4 = 1
7. Updated subscriber:
update T3 set c4=0
8. Run merge agent. The row at the sub was removed and the row at pub was
updated. This is by design. At any time, as soon as a row becomes
dissatisfied to the filter condition, it will be removed from the
subscriber table.
After Step 6, if I run step 7, before run merge agent, make another update
statement:
update T3 set c1=0, c2=3 where c1=1
Then run merge agent, I see the row in subscriber was removed and the row
in publisher was updated.
In your case, your first action (below) should remove the row from the
subscriber, then you won't have a chance to do the other updates:
Action:
DNLD_MERG_CD is set to 0.
Merge is executed.
Result:
Merge code is 0 on both databases (expected)
Please test again. If confirmed, please let me know all the versions of
your publisher, subscriber and distributor (I am using 818 for all), and if
possible, send your table schemas and replication script to me for further
check.
Regards,
Lan Lewis-Bevan
MS SQL support
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Lan,
Thank you for looking into this problem. There's an extra wrinkle in
my scenario that could affect the result. My filter condition is on
another table. The filter condition excludes rows based on values in
the second table, yet because of the filter join, those excluded rows
are appropriately included in the replication. You may need to add
that second table to see the same results.
I posted my table defns and replication scripts to this newsgroup and
received a reply from Baisong Wei that they would try to duplicate the
error (see 5/18, appears as a separate thread in Agent Newsreader). I
have not heard back from them. I'd love to get this solved sooner,
but don't want to see you duplicating effort.
We have version 8.00.760 on our server and locally. I believe this is
sp3. Where do I obtain v8.18? It would be worth a retest on that
version.
Kees
@.@.version on publisher:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack
4)
@.@.version on subscriber:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Personal Edition on Windows NT 5.1 (Build 2600: Service Pack
1)
On Tue, 25 May 2004 01:36:27 GMT, lanlb@.online.microsoft.com (Lan
Lewis-Bevan [MS]) wrote:

>Hi Kees,
>I checked your replication logic, couldn't figure out why the replication
>behaves like that.
>So I setup a merge replication to test, using one table:
>1. Create the merge table:
>create table T3 (c1 int, c2 int, c3 char(8), c4 int)
>insert T3 values (1, 1, 'one', 0)
>2. Checked the filter statement:
>Select * from T3 where c1 in (select c1 from T3 where c3 = 'one' and c4 =1)
> => 0 row returned
>Select * from T3 where c3 = 'one' and c4 =1 => 0 row returned.
>3. Setup a merge replication for this table (T3) with row filter:
>SELECT <published_columns> FROM [dbo].[T3] WHERE c1 in (select c1 from T3
>where c3 = 'one' and c4 = 1)
>4. Run Snapshot and merge agents. Checked the subscriber table, no row was
>copied, This is as expected, since no row satisfies the filter condition..
>5. updated publisher:
>update T3 set c4=1
>6. Run merge agent, one row went to the sub, as expected. Now both pub and
>sub has one row with c4 = 1
>7. Updated subscriber:
>update T3 set c4=0
>8. Run merge agent. The row at the sub was removed and the row at pub was
>updated. This is by design. At any time, as soon as a row becomes
>dissatisfied to the filter condition, it will be removed from the
>subscriber table.
>After Step 6, if I run step 7, before run merge agent, make another update
>statement:
>update T3 set c1=0, c2=3 where c1=1
>Then run merge agent, I see the row in subscriber was removed and the row
>in publisher was updated.
>
>In your case, your first action (below) should remove the row from the
>subscriber, then you won't have a chance to do the other updates:
>Action:
>DNLD_MERG_CD is set to 0.
>Merge is executed.
>Result:
>Merge code is 0 on both databases (expected)
>
>Please test again. If confirmed, please let me know all the versions of
>your publisher, subscriber and distributor (I am using 818 for all), and if
>possible, send your table schemas and replication script to me for further
>check.
>Regards,
>
>Lan Lewis-Bevan
>MS SQL support
>This posting is provided "AS IS" with no warranties, and confers no rights.