Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
thebeezle
Starting Member
1 Post |
 Posted - 2012-04-22 : 13:53:37
|
| 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. |
|
|
YazanAllahham
Starting Member
2 Posts |
Posted - 2012-04-28 : 21:06:14
|
| 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 defliquor hij klmyour replication created the snapshot and synced the records with the subscriber and now your publisher and subscriber are identicalok...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 klmthe 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 syncedthat's exactly how it does work.-----------------------------------------------------------------Being a computer software developer doesn't mean you can fix a TV |
 |
|
|
|
|
|
help merge filter process flow