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?
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?