Horizontal filtering a merge publication.
-
I'm trying to filter horizontally some tables that are being replicated (in SQLServer 2000), but without much success. The tables to be filtered (SS_ResourceFoo, SS_ResourceBar) have a column called Res_StudioMask, which is a bit mask. I then have a table called SS_Studios which maps HOST_NAME() server names to a bit value (1,2,4,8,16,...) I'd like rows from the filtered Resource* tables to only goto to subscribers based upon the studio bitmask column.
I'm not sure if I should be doing dynamic filters or join filters. The horizontal partitions of data will definitely overlap and whether rows to be deleted from a subscriber if they are removed from the partition is not so critical.
At the moment I'm trying a join filter like this:-
(SS_Studio.MRes_EnumID & SS_ResSet.Res_StudioMask) = SS_Studio.MRes_EnumID and SS_Studio.MRes_Server = HOST_NAME()
The dynamic filter on SS_Studio is just '1 = 1' as I need all rows at all subscribers.
Any ideas, what could be up? Am I on the right path?
Thanks,
Arthur.
-
More fixing of my own problems...
Think I've sorted this one, for some reason I think HOST_NAME() was returning something that doesn't match my Studios table. When I overid it on the command line of the merge agent it works.
Now if I open an query window to the server, HOST_NAME() just returns my computer name, obviously. But SERVERPROPERTY('servername') returns what I should expect for that server.
Could it be down to it being a push subscription? I guess with the agent then running on the distribution server, does HOST_NAME() just return the distro servers name?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply