views:

20

answers:

0

I have a Win Form, Data Entry application that uses 4 separate databases. This is an occasionally connected app that uses Merge Replication (SQL 2005) to stay in Sync. This is working just fine. The next hurdle I am trying to tackle is adding Filters to my Publications.

Right now we are replicating 70mbs, compressed, to each of our 150 subscribers when, truthfully, they only need a tiny fraction of that. Using Filters I am able to accomplish this (see code below), but I had to make a mapping table in order to do so. This mapping table consists of 3 columns: A PrimaryID(Guid), WorkerName(varchar), and ClientID(int).

The problem is, I need this table present in all FOUR Databases in order to use it for the filter since, to my knowledge, views or cross-db query's are not allowed in a Filter Statement.

What are my options?

It seems like I would set it up to be maintained in 1 Database and then use Triggers to keep it updated in the other 3 Databases. In order to be a part of the Filter I have to include that table in the Replication Set so how do I flag it appropriately?

Is there a better way altogether?

SELECT <published_columns>
FROM [dbo].[tblPlan]
WHERE [ClientID] IN
        (
         SELECT ClientID
         FROM [dbo].[tblWorkerOwnership]
         WHERE WorkerID = SUSER_SNAME()
        )

Which allows you to chain together Filters, this next one is below the first one so it only pulls from the first's Filtered Set.

SELECT <published_columns>
FROM [dbo].[tblPlan]
INNER JOIN [dbo].[tblHealthAssessmentReview] ON
    [tblPlan].[PlanID] = [tblHealthAssessmentReview].[PlanID]

P.S. - I know how illogical the DB structure sounds. I didn't make it. I inherited it and was then told to make it a "disconnected app."