merge subscriber keeps data outside the filter

merge subscriber keeps data outside the filter

Post by Bhanoji Mall » Fri, 04 May 2001 00:46:58



Version:
 Microsoft SQL Server  2000 - 8.00.194 (Intel X86)
 Aug  6 2000 00:57:48
 Copyright (c) 1988-2000 Microsoft Corporation
 Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 1)
Problem:
I am merge replicating two tables t1 and t2.  t1 has two fileds rowid and
data.  t2 has two fields user and rowid.  I am applying merge filter on t2
as t2.user = suser_sname() and join filter on t1 as t1.rowid = t2.rowid.
Sample data as follows

t1 data
rowid   data
1            one
2            two
3            three
4            four
5            five
t2 data
user      rowid
u1        1
u1        2
u1        3

After synchronizing, subscriber has three rows for table t1.  Then on the
publisher I deleted the row u1, 3 in table t2.  Now t2 has the following
data
new data for t2
user        rowid
u1            1
u1            2

I synchronized the merge replication.  Subscriber still has 3 rows in table
t1.  Subscriber is not deleting the rows which are out of the filter.
However if I change the data for rowid =3 on publisher then it deletes that
row at the subscriber.  Is there any way to delete the rows at the
subscriber which fall out of the filter without updating them at the
publisher?  Thanks.

 
 
 

merge subscriber keeps data outside the filter

Post by Tom Michael » Fri, 04 May 2001 04:31:46


I recreated your scenario on 8.00.0194, but could not reproduce your
problem.  After deleting the row from t2 and running the merge agent, I was
left with two rows in t1 and two rows in t2 on the subscriber.  The join
filter appeared to work properly.  I had to assume that you are using a
known pull subscription.

One little snag that I ran into is that the column name 'user' is a reserved
word.  When I ran the following delete statment, no rows were deleted...

delete from t2
where user = 'u1'
and rowid=3

I had to put 'user' in brackets to make it work properly..

delete from t2
where [user] = 'u1'
and rowid=3


Quote:> Version:
>  Microsoft SQL Server  2000 - 8.00.194 (Intel X86)
>  Aug  6 2000 00:57:48
>  Copyright (c) 1988-2000 Microsoft Corporation
>  Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 1)
> Problem:
> I am merge replicating two tables t1 and t2.  t1 has two fileds rowid and
> data.  t2 has two fields user and rowid.  I am applying merge filter on t2
> as t2.user = suser_sname() and join filter on t1 as t1.rowid = t2.rowid.
> Sample data as follows

> t1 data
> rowid   data
> 1            one
> 2            two
> 3            three
> 4            four
> 5            five
> t2 data
> user      rowid
> u1        1
> u1        2
> u1        3

> After synchronizing, subscriber has three rows for table t1.  Then on the
> publisher I deleted the row u1, 3 in table t2.  Now t2 has the following
> data
> new data for t2
> user        rowid
> u1            1
> u1            2

> I synchronized the merge replication.  Subscriber still has 3 rows in
table
> t1.  Subscriber is not deleting the rows which are out of the filter.
> However if I change the data for rowid =3 on publisher then it deletes
that
> row at the subscriber.  Is there any way to delete the rows at the
> subscriber which fall out of the filter without updating them at the
> publisher?  Thanks.


 
 
 

merge subscriber keeps data outside the filter

Post by TSVK » Fri, 04 May 2001 04:32:39



This should help you.


Quote:> Version:
>  Microsoft SQL Server  2000 - 8.00.194 (Intel X86)
>  Aug  6 2000 00:57:48
>  Copyright (c) 1988-2000 Microsoft Corporation
>  Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 1)
> Problem:
> I am merge replicating two tables t1 and t2.  t1 has two fileds rowid and
> data.  t2 has two fields user and rowid.  I am applying merge filter on t2
> as t2.user = suser_sname() and join filter on t1 as t1.rowid = t2.rowid.
> Sample data as follows

> t1 data
> rowid   data
> 1            one
> 2            two
> 3            three
> 4            four
> 5            five
> t2 data
> user      rowid
> u1        1
> u1        2
> u1        3

> After synchronizing, subscriber has three rows for table t1.  Then on the
> publisher I deleted the row u1, 3 in table t2.  Now t2 has the following
> data
> new data for t2
> user        rowid
> u1            1
> u1            2

> I synchronized the merge replication.  Subscriber still has 3 rows in
table
> t1.  Subscriber is not deleting the rows which are out of the filter.
> However if I change the data for rowid =3 on publisher then it deletes
that
> row at the subscriber.  Is there any way to delete the rows at the
> subscriber which fall out of the filter without updating them at the
> publisher?  Thanks.

 
 
 

merge subscriber keeps data outside the filter

Post by Bhanoji Mall » Fri, 04 May 2001 06:22:52


I am sorry.  I did not give the following information in my original
message.  You are right it works for known subscription.  My subscription is
anonymous and my subscriber is MS Access database (Jet 4.0 engine).  In this
scenerio it is not working.  I checked with Sql server known subscription
and it worked alright.  Thanks.


> I recreated your scenario on 8.00.0194, but could not reproduce your
> problem.  After deleting the row from t2 and running the merge agent, I
was
> left with two rows in t1 and two rows in t2 on the subscriber.  The join
> filter appeared to work properly.  I had to assume that you are using a
> known pull subscription.

> One little snag that I ran into is that the column name 'user' is a
reserved
> word.  When I ran the following delete statment, no rows were deleted...

> delete from t2
> where user = 'u1'
> and rowid=3

> I had to put 'user' in brackets to make it work properly..

> delete from t2
> where [user] = 'u1'
> and rowid=3



> > Version:
> >  Microsoft SQL Server  2000 - 8.00.194 (Intel X86)
> >  Aug  6 2000 00:57:48
> >  Copyright (c) 1988-2000 Microsoft Corporation
> >  Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 1)
> > Problem:
> > I am merge replicating two tables t1 and t2.  t1 has two fileds rowid
and
> > data.  t2 has two fields user and rowid.  I am applying merge filter on
t2
> > as t2.user = suser_sname() and join filter on t1 as t1.rowid = t2.rowid.
> > Sample data as follows

> > t1 data
> > rowid   data
> > 1            one
> > 2            two
> > 3            three
> > 4            four
> > 5            five
> > t2 data
> > user      rowid
> > u1        1
> > u1        2
> > u1        3

> > After synchronizing, subscriber has three rows for table t1.  Then on
the
> > publisher I deleted the row u1, 3 in table t2.  Now t2 has the following
> > data
> > new data for t2
> > user        rowid
> > u1            1
> > u1            2

> > I synchronized the merge replication.  Subscriber still has 3 rows in
> table
> > t1.  Subscriber is not deleting the rows which are out of the filter.
> > However if I change the data for rowid =3 on publisher then it deletes
> that
> > row at the subscriber.  Is there any way to delete the rows at the
> > subscriber which fall out of the filter without updating them at the
> > publisher?  Thanks.

 
 
 

merge subscriber keeps data outside the filter

Post by Tom Michael » Sat, 05 May 2001 07:01:27


Turns out there is a way to make this work.  When creating your publication

true, the default is false.  In enterprise Manager, this parameter is set in
the publication wizard after you add your filters.  On the wizard window
that says, "Optimize Syncronization" you are asked "Do you want to minimize
the amount of data sent over the network?", the default is no.  Change this

sp_addmergepublication in BOL.  It adds some overhead to your publisher, but
allows the merge agent to catch child record deletes for non-SQL
subscribers.

I was able to successfully have the child record deleted from t1 on my
Access subscriber.
Tom


> I am sorry.  I did not give the following information in my original
> message.  You are right it works for known subscription.  My subscription
is
> anonymous and my subscriber is MS Access database (Jet 4.0 engine).  In
this
> scenerio it is not working.  I checked with Sql server known subscription
> and it worked alright.  Thanks.



> > I recreated your scenario on 8.00.0194, but could not reproduce your
> > problem.  After deleting the row from t2 and running the merge agent, I
> was
> > left with two rows in t1 and two rows in t2 on the subscriber.  The join
> > filter appeared to work properly.  I had to assume that you are using a
> > known pull subscription.

> > One little snag that I ran into is that the column name 'user' is a
> reserved
> > word.  When I ran the following delete statment, no rows were deleted...

> > delete from t2
> > where user = 'u1'
> > and rowid=3

> > I had to put 'user' in brackets to make it work properly..

> > delete from t2
> > where [user] = 'u1'
> > and rowid=3



> > > Version:
> > >  Microsoft SQL Server  2000 - 8.00.194 (Intel X86)
> > >  Aug  6 2000 00:57:48
> > >  Copyright (c) 1988-2000 Microsoft Corporation
> > >  Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 1)
> > > Problem:
> > > I am merge replicating two tables t1 and t2.  t1 has two fileds rowid
> and
> > > data.  t2 has two fields user and rowid.  I am applying merge filter
on
> t2
> > > as t2.user = suser_sname() and join filter on t1 as t1.rowid =
t2.rowid.
> > > Sample data as follows

> > > t1 data
> > > rowid   data
> > > 1            one
> > > 2            two
> > > 3            three
> > > 4            four
> > > 5            five
> > > t2 data
> > > user      rowid
> > > u1        1
> > > u1        2
> > > u1        3

> > > After synchronizing, subscriber has three rows for table t1.  Then on
> the
> > > publisher I deleted the row u1, 3 in table t2.  Now t2 has the
following
> > > data
> > > new data for t2
> > > user        rowid
> > > u1            1
> > > u1            2

> > > I synchronized the merge replication.  Subscriber still has 3 rows in
> > table
> > > t1.  Subscriber is not deleting the rows which are out of the filter.
> > > However if I change the data for rowid =3 on publisher then it deletes
> > that
> > > row at the subscriber.  Is there any way to delete the rows at the
> > > subscriber which fall out of the filter without updating them at the
> > > publisher?  Thanks.

 
 
 

1. Merge Replication with join filters: Orhpaned rows at subscriber

I am having a problem with Merge Replication using SQL Server 2000 (with
SP1) and I'm hoping someone here will have a view on it.

The schema has the following tables:

 Salesman (idSalesman(PK),forename,surname,systemname)
 Client   (idClient(PK),forename,surname)
 Manages  (idSalesman,idClient(both in PK))

Each Salesman has a laptop. The 'systemname' column is populated with the
machine name of the laptop when it is issued to the Salesman.

Row filter of 'systemname = host_name()' on Salesman
Join filter of 'Manages.idSalesman = Salesman.idSalesman' on Manages
Join filter of 'Client.idClient = Manages.idClient' on Client

This partitions the data so that a salesman has all the clients of which he
is the manager.

This all works as I would expect. Clients can be reassigned between salesmen
simply by changing the value Manages.idSalesman. The original salesman's
laptop has the Client and Manages rows removed, the new salesman's laptop
has them added. Everything is fine so far.

If I delete a row from Manages AT THE PUBLISHER, the Manages row is deleted
from the laptop and the Client row is removed. Again, this is what I would
expect.

If, however, I delete a Manages row AT THE SUBSCRIBER the Manages row is
deleted at the published (as expected) but the associated Client row remains
at the subscriber.

If I ADD a new Client row at the subscriber without a corresponding Manages
row it is replicated to the publisher and removed from the laptop. This is
as I would expect as it doesn't fit with the filtering rules I've defined.

Earlier today I was able to get the orphaned client rows removed from the
laptop by editing the rows at the publisher. This seemed to force merge
replication to reevaluate the subscribers that needed the row and to remove
it from the laptop. This, however, doesn't seem to happed since I installed
SP1, but I'm not 100% sure of that.


sp_addmergepublication set to true and to false with the same results, which
is also puzzling. When set to false I would have expected the same problem
to occur when removing Manages rows at the publisher.

Any got any ideas?

2. Newbie: Paradox and application

3. Join Filtered data disappears off of subscriber

4. Access DB getting slow

5. FIX: Dynamic Filtered Tables Do Not Replicate Data From Joined Tables to SQL Server CE Subscribers

6. Mapping a Database

7. I don't merge data from Subscriber to Publisher

8. encryption

9. Merge replication and old data on subscriber

10. Merge replication does not copy data to all subscribers

11. Merge Replication with Dynamic Filter and Row Filter failure on two Deletes

12. Problem with filters and join filters in merge replication

13. Filtering data during merge replication