Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am looking to replicate the results of a query, is that possible or do I need to rep the tables themselves?

share|improve this question

2 Answers 2

Yes its definately possible. We use it all the time for replicating warehouse data to 53 different warehouses. The query you are talking about is called a replication filter.

Now listen up!!! Do not put your query in the filter. Use a view instead.

That means you can easily alter the view at a later date as filters cannot be changed. So you filter will be "select cols from vwUserFilter"

If you put the query in the filter and want to change it, you'd have to drop the publication and re-create it which is very painful.

Also see this link

share|improve this answer
3  
Good tip on using the view to prevent recreating the publication. –  StanleyJohns Nov 25 '11 at 15:48
    
Using a view don't I have to replicate the underlying tables? That's what I learned from trying to replicate a view earlier. I noticed the filter but I didn't think I could use that as it seems to need some table to start with. –  HolidayHarry Nov 25 '11 at 16:45
    
Off course you need to replicate the tables. Views are nothing but a persistent alias of a select statement (except for when they have an index, off course). –  Marian Nov 25 '11 at 23:03
    
@HolidayHarry No you don't. The filtering is done at the server, not at the client. The primary database will only send down data to the client db's that pass the filters. –  Simon Hughes Nov 29 '11 at 14:04
    
Has someone used a view for merge replication filtering? I cannot edit my filter with the level of control required for this. I am using SQL 2012. I can only edit after the where clause and thus cannot specify a view. Is there a blog explaining this? –  peter Jun 28 '12 at 22:11

To replicate the results of a query, store the results in a table, and replicate that table. Yes the table must exist on the other servers. I suggest using snapshot replication for this type of scenario.

share|improve this answer

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.