Interactions between subset and join filters

I'm running into problems with a merge publication where I am trying to use
both subset (dynamic) filters and join filters. I was hoping that the join
filters operated on the slice of data carved out by the subset filters, but
that doesn't appear to be happening. Instead, the union of the results of
the subset filter on the entire database and the dynamic filter on the
entire database is what is synchronized.
For example, assume I have the following three tables with the listed
columns:
- User (Id, Name)
- Entry (Id, Date, UserId, SignatureId)
- Signature (Id, Date, Data)
The goals of the filters are as follows:
- Only retrieve the entry rows that are less than 30 days old.
- Only retrieve the signature rows that are less than 30 days old.
- Only retrieve the entry rows for that particular user.
I setup subset filters for the following tables:
Entry: ... WHERE Date > (GETDATE() - 30)
Signature: ... WHERE Date > (GETDATE() - 30)
I also setup a join filter to only get a particular user's Entries:
... INNER JOIN [dbo].[Entry] ON Entry.UserId = User.Id and
User.Name = HOST_NAME()
(Assume I pass in the User's name as the HOST_NAME.)
When I synchronize with SQL CE, I find that I do indeed get only the Entry
rows associated with that user, but I get ALL of that user's Entry rows,
regardless of the Date.
In addition, I only get Signatures that are less than 30 days old. The
problem is the old Entry rows point to Signature rows that do not exist as
they were filtered out, and I receive an error stating that "The row update
or insert cannot be reapplied due to an integrity violation" (Error 28549)
My question boils down to this:
In what order and on what subsets of data do filters get applied?
More specifically, how can I set up filters with this schema to avoid the
integrity violation problem?
Thanks for your time.
- Eli
Hi Eli,
i think you should read my post "Push referenced records" since we're facing
a similar problem. The solution for us was to eliminate the foreign key
constraints (all of them), because you can not ensure the order of
insertation when the snapshot arrives to the subscriber. In addition we tried
to extend the foreign key definitions with the "NOT FOR REPLICATION" option -
because we didn't want to drop the constraints - but this option is not
supported in CE. ... we also had to eliminate the triggers - fortunately "NOT
FOR REPLICATION" is supported for triggers.
When creating the replication you have to specify the function that is
filtering the records per subscribers (e.g. SUSER_SNAME(), HOST_NAME()). I
think you should put the "User.Name = HOST_NAME()" clause to the rowfilter.
Filtering by date is still a problem to solve. We have the same situation...
regards
pierre
|||Thanks for the reply, Pierre.
I solved the problem by eliminating the subset filters on tables that
already have join filters, and moved the filtering logic into the join
filter. For example, I added the last line to my join filter:
... INNER JOIN [dbo].[Entry] ON Entry.UserId = User.Id and
User.Name = HOST_NAME()
AND Entry.Dt > (GETDATE() - 30)
Thanks again,
- Eli
"Pierre Packard" <Pierre Packard@.discussions.microsoft.com> wrote in message
news:4A13E8A8-B3F0-415B-9560-2C20AB78B6F6@.microsoft.com...
> Hi Eli,
> i think you should read my post "Push referenced records" since we're
facing
> a similar problem. The solution for us was to eliminate the foreign key
> constraints (all of them), because you can not ensure the order of
> insertation when the snapshot arrives to the subscriber. In addition we
tried
> to extend the foreign key definitions with the "NOT FOR REPLICATION"
option -
> because we didn't want to drop the constraints - but this option is not
> supported in CE. ... we also had to eliminate the triggers - fortunately
"NOT
> FOR REPLICATION" is supported for triggers.
> When creating the replication you have to specify the function that is
> filtering the records per subscribers (e.g. SUSER_SNAME(), HOST_NAME()). I
> think you should put the "User.Name = HOST_NAME()" clause to the
rowfilter.
> Filtering by date is still a problem to solve. We have the same
situation...
> regards
> pierre
>