June 17, 2009 at 10:05 am
I'm wondering what the best way is to accomplish the following filter:
I have a Client table that is joined to a ClientLocation table. A client can be a joined to many ClientLocation records, and each replication subscription matches a Location.
So, the ClientLocation table is filtered based on the LocationId (matches HOST_NAME of the subscription PC). The Client table is joined to the ClientLocation record that is filtered, so that each subscription gets all of their ClientLocation records, which also gives them the matching Client records.
That is simple enough. But, now let's say I have an Invoice table that joins to the client. The Invoice table has a "CreatedAtLocationId" that determines what subscription the invoice was created at, and as well it has a "ClientId" that joins the record to the Client Table. This is needed because a subscription can create an 'Invoice' record for a 'Client' at another subscription, and I need the Invoice to go stay at the source subscription, and also be sent out joined to the 'Client' to any other subscriptions that the record belongs to.
I want to filter the Invoice table so that all invoices that match a "Client" record get sent to that subscription (which is easy with a JOIN filter). However, I want an additional condition so that it will also be sent out if "CreatedAtLocationId" matches the HOST_NAME of the subscription.
I have about a dozen tables that would require this condition. Any ideas?
EDIT: I've found conflicting resources that may hold a solution to my problem.
http://www.replicationanswers.com/MergeNonConvergence.asp
consider 2 tables: Customers and Orders. These are joined by a PK - FK relationship on CustomerID so that as we'd expect a customer can place several orders. If I create a join filter to link the tables, all 'ordering' Customers and their associated Orders are replicated. I then add a filter clause on the Customers table (parent) to restrict the output to a particular customer: CustomerID = 'Joe'. The resulting (automatically created) merge view for Orders restricts the results to the orders belonging to customer 'Joe'. So, on initialization one customer and his associated orders are replicated. However, what if I add another filter now to the Orders table: CustomerID = 'Harry'? Logically this should further restrict the replicated orders and we'd expect zero records returned. However, on initialization we find that more orders are now replicated! In this case the merge view is created which UNIONs the 2 clauses, rather than using a logical AND clause! This is quite different to the above scenarios - we now observe extra rows rather than seeing rows unexpectedly deleted.
So the scenario above means I can create a join filter from my 'Invoice' table to my 'Client' table, and then also create a parameterized filter for my 'Invoice' table based on the 'CreatedAtLocationId'. This will UNION the two results, and is what I had hoped for. However, from Microsoft:
http://msdn.microsoft.com/en-us/library/ms152478.aspx
The article should only have one parameterized filter or join filter. Having a parameterized filter and being the parent in a join filter is allowed. Having a parameterized filter and being the child in a join filter is not allowed. Having more than one join filter is also not allowed.
In my case, and the above example, we're talking about adding a parameterized filter to the child in a join filter. I believe it will let me do this, but I'm not sure of the implications. Has anyone else ever needed to combine filters before?
June 18, 2009 at 4:05 pm
So after doing some testing, I've found the behavior that I want, even though MSDN says that it cannot (or should not) be done.
I have a table (Invoice) that is a child in a join filter (it's joined to Client), and it is also a parameterized filter (where LocationId = HOST_NAME()). The behavior that I get is a basically a UNION. It filters out all Invoice records that belong to each Client, as well it is sent out to any studio where the LocationId matches the host name.
I need this type of behavior in at least a half dozen articles, but why does MSDN say not to do it? What does this mean to my replication system? Am I building a timebomb?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply