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 using merge replication with SQL 2012. I have a number of articles in my publication and will have to come up with quite an elaborate set of filters.

If I add a filter on my article it can contain a sub query, but I noticed it doesn't pick up on changes properly.

For instance Table1, and Table2.

Filter on Table1 is

select * from Table1 where id in (select id from Table2)

If more records are added to Table2 I would want the contents of Table1 to be changed accordingly when the sync process next occurs.

I found out that this is not working. Is it because when I tried it Table2 was not an article in my merge replication publication, or is it because merge replication doesn't support subqueries like this and recognising when a filter has changed.

What it would mean is that the sync process would have to scan each filter, and work out what tables the subquery relates to and figure out whether these have changed. Does it do something like this?

UPDATE:

I have tried a number of things, but they don't work.

1) Subquery - doesn't work. Here it confirms that this won't work
    http://support.microsoft.com/kb/324362
2) Subquery calling a function - doesn't work
3) Subquery calling a function which takes a guid parameter which I change every time - doesn't work

So the only other option mentioned is a view, but I cannot get the view to work either.

The first problem is that if I replicate a view it just gets replicated as a view and the underlying table needs to be there. But Simon I read your other post and that is not what you are saying.

I think what you are saying is that I should be able to select a table in my replication publication, and then filter it based on a view like this,

select cols from vwUserFilter

But when I look in management studio I see a filter for my table which looks like this,

SELECT <published_columns> FROM [dbo].[Berm] WHERE 

And that part of my filter is fixed, I cannot edit [dbo].[berm] and replace it with a view.

So what I think you are suggesting is that I can change the above to,

SELECT <published_columns> FROM [dbo].[BermView]

And the BermView is a view which filters the Berm table. Is that right, and if so how?

share|improve this question
    
Correct, you should set the replication filter to use a view "select <published_columns> from viewName" instead of "SELECT <published_columns> FROM tableName" –  Simon Hughes Jun 28 '12 at 10:59
    
By using a view, it will allow you to alter the view at a later date without having to stop, re-publish and re-subscribe. –  Simon Hughes Jun 28 '12 at 11:00
    
But my point is that I cannot update the replication filter with that level of control. All I can update is the where clause, perhaps you are not using merge replication, or else it has changed in SQL 2012 perhaps? –  peter Jun 28 '12 at 20:48
add comment

2 Answers

It should work ok. Try using a view for your filter instead. See this article here

share|improve this answer
    
Hi Simon, I have updated the question for you. I cannot get a view to work. The start of the filter is fixed, and I cannot add a view in there. –  peter Jun 28 '12 at 0:01
add comment

My thoughts

  • You could replicate a view as a table, however to my opinion you must use a indexed view. However an index view has some restrictions like no outer joins, so this might not be an option.

http://sqlship.wordpress.com/2010/04/14/replicate-indexed-views-transactional-replication/

  • Merge join filters. Rewrite your subselect to a join.

  • Filter locally, use a scheduled tablediff.exe to compare with local second table and replicate the second table.

share|improve this answer
add comment

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.