0

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?

Improve this question
3
  • 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
    Commented Jun 28, 2012 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
    Commented Jun 28, 2012 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
    Commented Jun 28, 2012 at 20:48

2 Answers 2

Reset to default
1

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

Improve this answer
1
  • 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
    Commented Jun 28, 2012 at 0:01
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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