SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Replication (2005)
 help merge filter process flow
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

thebeezle
Starting Member

1 Posts

Posted - 04/22/2012 :  13:53:37  Show Profile  Reply with Quote
Hi, I hope someone can help explain how merge filters work.

My problem (simplified and reproduced) is this... I have a filter on a customer table that filters by name for example... i.e. select from customerTable where customer name like '%liquor%'

I can initialize the snapshot and my subscriber on (windows CE device) and I do see that the filter yields only customers with 'liquor' in their name. Good.

I can 'update' a different customer record and add 'liquor' to their name, sync my subscriber and indeed I get 1 new record downloaded and inserted. Good.

Now, if I take that same record and update the name to remove the word 'liquor' from the name and sync my subscriber, I *do not* see that same record deleted from the subscriber. I just get 0 inserts, updates and deletes. Not good.

There is just something I don't understand about the flow of how and when the filters are checked. Or I am doing something wrong but I have no clue what.

Can anyone shed some light on this for me? Much thanks!

More info.... I am using parametrized filters and joining this customer table based on salesperson group table in a many to many join... but I also have the filters checking for active customers in an 'Active' view so that I can weed out customers for various reasons (i.e. expired etc.) which helps keep the number of records down.

SELECT <published_columns>
FROM [dbo].[SalespersonGroupDetail]
INNER JOIN [dbo].[Customer]
ON [SalespersonGroupDetail].[SalespersonNo] = [Customer].[SalespersonNo]
AND
EXISTS (SELECT [Customer].[CustomerNo]
FROM [Customer_ActiveOnly]
WHERE
([Customer].[CustomerNo] = [Customer_ActiveOnly].[CustomerNo])))

Maybe this configuration isn't supported or something but it is working ok, just not yielding what appear to be correct results for me.

After more testing, it appears that any change to the record set at the publisher that causes a record to be inserted or updated at the subscriber seems to work. However any change at the publisher that would cause a record to be deleted at the subscriber doesn't seem to generate the corresponding delete. delete_tracking is enabled. Even an actual 'delete' record at the publisher is not being propagated to the subscriber.

Edited by - thebeezle on 04/23/2012 09:34:04

YazanAllahham
Starting Member

Syria
2 Posts

Posted - 04/28/2012 :  21:06:14  Show Profile  Reply with Quote
Hi,
Since your merge replication has to filter the table to obtain the records that only have "[customer name] like '%liquor%'" condition then it will apply the transactions on the records that ONLY match the above condition.

when you removed the "liquor" word from your record the replication agent couldn't transfer that modification to the subscriber because when the agent could not find a record to transfer its modification since the select statement only fetch records that only match above condition.

to make it simple here is an example:

Customers Table has 2 records:
[Customer Name]
-----------------
abc liquor def
liquor hij klm

your replication created the snapshot and synced the records with the subscriber and now your publisher and subscriber are identical

ok...now say you updated the 1st record and your table become :

[Customer Name]
-----------------
abc def (this record has been updated and your update was removing the "liquor" word)
liquor hij klm

the merge agent will catch the modification but will not be able to sync it because it doesn't match your condition because you have just removed the "liquor" word
so the record will not be synced

that's exactly how it does work.

-----------------------------------------------------------------
Being a computer software developer doesn't mean you can fix a TV

Edited by - YazanAllahham on 04/28/2012 21:07:03
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000