We know that the Merge Replication of SQL Server 2000 supports the functionality of Dynamic Filter. If we have multiple subscriber, each subscriber only needs to maintain your specific data, then we only need to create a publication, then use the Dynamic Filter's function to copy the specific data required for each subscriber. The most common system function for Dynamic Filter is SUSER_SNAME () and host_name (), and the SQL Server calculates the SUSER_SNAME () or host_name () value of a specific MERGE Agent connection for each subscriber's Merge Agent, SQL Server calculates a SUSER_SNAME () or host_name () value () value () value. The data is then filtered according to the results.
Direct use of SUSER_SNAME () or host_name (), it is necessary to have a value in which the corresponding SUSER_SNAME () or HOST_NAME () is required in Table. For example, if our Publisher Server called Shanghai to maintain all data, three subscriber is called Nanjing, Suzhou, and Hangzhou, respectively, and if we want to use Host_Name () to copy data to three subscriber, our table should There is a list of host_name (), then we can filter data with Dynamic Filter as "filter_column = host_name ():
Create Table Orders (ORDERID INT NOT NULL, FILTER_COLUMN CHAR (20), Quantity Int
Go
INSERT INTO Orders Values (1, '
nanjing
', 100)
Insert Into Orders Values (2, '
Suzhou
', 200)
Insert Into Orders Values (3, '
Hangzhou
', 120)
INSERT INTO Orders Values (4, '
Hangzhou
', 100)
Go
However, if the data in our table does not just match SUSER_SNAME () or Host_Name (), how should we do Dynamic Filter?
For example, the following table, only one ZoneID can be used to distinguish data needed by Subscriber, but its value and SUSER_SNAME () or Host_Name () are different.
Create Table Orders (Orderid Int Not Null, Zoneid Nvarchar (5), Quantity Int
Go
Insert Into Orders Values (1, N'1 ', 100)
Insert Into Orders Values (2, N'2 ', 200)
Insert Into Orders Values (3, N'3 ', 120)
Insert Into Orders Values (4, N'3 ', 100)
To do Dynamic Filter for this data, we can use the Merge Agent's parameters, HostName to implement it. SQL Server Books Online does not understand the usage of this parameter. When we don't specify this parameter for merge agent, the host_name () function connected to MERGE Agent returns the computer name of the server where Merge Agent is located; when we specify this parameter for the merge agent, the host_name () function returns the value specified by the hostname parameter. .
For example, if we want to copy ZoneID = N'1 'record to nanjing, we can still use "zoneid = host_name ()" Filter, just add "-hostname = 1" parameters for Nanjing Merge Agent. To add this parameter, right-click Merge Agent, select Agent Properties, select the STEPS option page, double-click the Run Agent, and then add "-hostname 1" in Command.
See MSDN for all available parameters for Merge Agent.
