I have a Win Form, Data Entry, application that uses 4 seperate Data Bases. 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?
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."
