We have a merge publication system in SQL 2008 R2. Publication is Enterprise Edition, Client's are Express Edition.
Basically, we have 3 tables Roles, UserRoles,Users.
Role : RoleID, RoleName columns
User : UserID, UserName columns
UserRole : UserID,RoleID columns
I want to filter these tables by HOST_NAME() function, which is equal to UserID for each subscription.
IF i set Dynamic Filtering for User table (UserID=HOSTNAME()) , i receive only 1 row in User table in subscription database, ok.
And also i set same Filter to UserRole table, and i can get only needed RoleID's to UserRole table, ok.
But when i want to receive only necessary Roles to subscription database Role table, it does not work.
I tried to use a Subquery, but i understood that it is a static filtering method.
I tried Join filter for Role table, but i always receive all roles to Role table. I tried "SELECT <published_columns> FROM [dbo].[Role] INNER JOIN [dbo].[UserRole] ON [Role].[RoleID] = [UserRole].[RoleID]". But it receives all roles to Role
table. I tried several Join filters for these tables but can't find a solution.
How can i handle this?
View Complete Post