Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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]
share|improve this question

2 Answers 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.

share|improve this answer
    
thank you for the idea. All my DB's are on the same server instance I just need data from one DB for the Filter on the others –  Refracted Paladin Sep 13 '10 at 14:43

http://www.sqlservercentral.com/articles/Advanced/designingcrossdatabasequeries/1753/

You are able to use cross DB queries in views.

share|improve this answer
    
**views** or cross-db query's are not allowed in a Filter Statement. Thanks for the response but I already knew that and that is, also, not my question. See quoted... –  Refracted Paladin Jul 6 '10 at 13:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.