Home / server / Questions / 128128
Accepted
Refracted Paladin
Asked: 2010-04-01 06:58:55 +0800 CST

Data from a table in 1 DB needed for filter in different DB

  • 772

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]
sql-server sql-server-2005 replication filtering merge-replication

2 Answers

  1. Best Answer
    • 1

  2. Depending on how quicky you need this filter data 'replicated' on the servers, you could create a job on the slave servers that updated the tables with data from the primary server. You wouldn't get updates right away like you would with triggers, but I think the design would be cleaner.

    E.g. cross database queries executed as jobs on the server, that do a simple

     DELETE FROM filterTable

    and then

     INSERT INTO filterTable ....

    to refresh the table used in the filters at a schedule you select.

    • 1