"parameterized filters" and "join filters" on the same table?

Question

"parameterized filters" and "join filters" on the same table?

N Cook

SSC Eights!

Points: 981
More actions
November 1, 2005 at 2:54 pm

#166611

Hi,
I ran into a situation recently where I needed to filter a table (used in merge replication) by more than one method.
Just to clarify, parametized filters are when you just filter a table based on a where clause that only references columns in that table, such as:
sp_addMergeArticle..........@subset_filterclause = N'[DateTime] >= dateadd(hh, -3, (SELECT getdate()))'
but what if the table that you need to put this condition on is already being filtered via join filters? Can you just add this subset_filterclause to the sp_addMergeArticle line? I tried it and the publication was created successfully, no error messages there. But when I tried to initialize subscribers I got data integrity errors violating primary key/foreign key constraints. (And, yes I changed UploadGenerationsPerBatch and DownloadGenerationsPerBatch to 2000) 🙂
Has anyone ever tried something similar to this? What is the workaround if this is an improper configuration?
For Example:
ParentTable (filtered via a parametized filter)
Child table (filtered via a join filter with Parent AND a subset filter)
Grandchild table (filtered via a join filter with the child table)
Thanks for any input!
Nate

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Troy Blakely Newbie Points: 5 More actions · Answer 1

Nate,

I've only created publications using the wizard in EM, but when trying to do filters similar, although not identical to yours my final solution was to do all the joining I needed to do in views and then run the paramaterized filter against the view. It is a bit slower, but it worked. Here's a sample of one of my parameter filters:

SELECT <published_columns> FROM [dbo].[tblContractItems] WHERE

ConCode in (Select ConCode FROM vContractItems WHERE

Region = 1)

N Cook SSC Eights! Points: 981 More actions · Answer 2

Thanks for your reply Troy! I was under the impression that subqueries in parametized filters are a "bad" thing. But how else are we supposed to accomplish certain types of filters?

Note the following quote from BOL, Replication, "Join Filters":

Although you can put a subquery into a row filter, it is not a join filter. If you update a row in a table referenced by a subquery, the query will not be re-evaluated and the row will not be propagated as part of replication.

I'm not 100% sure what that quote means, but I took it to mean that it's something I shouldn't do.

Using a view is definitely a novel idea to me though, so thank you for that! If anyone else has any other suggestions I would be grateful!

Nate

Programmer/Analyst (Accidental DBA)

Richmond, VA. USA

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

I have some Transaction Replications in which I select from TableA with a filter to select only those that are also in TableB:

SELECT <published_columns> FROM <<TableA>> WHERE

Acct# in (select Acct# from TableB

where TYPE = 'DIRECT')