Merge Replication Filter does not work at Subscriber

I am setting up a SQL Server Database Filter based Merge Replication between
Publisher (on Server Global) & Subscriber (on Server Site) using
EXCHANGE_TYPE '1' (Data only needs to be merged from Subscriber(Site) to
Publisher(Global) - Upload). You can consider this as a Central Subscriber
model with multiple Publications based on Filter "Siteid" push Data to
Central Subscriber Global. I have created all the Publications in the Central
Publisher Database Global (Normally central subscriber model will have
Publications on each site but I did wanted to do that due to Maintenance &
security issues )
Also I have set up conflict resolver "SQL Server Subscriber always wins the
conflict" since I want my Subscriber changes to override changes done at
Publisher.
However, I am facing this issue:
Even if I add a row at subscriber Site that is not part of Filter (it goes
into the Publisher Global). I do not want the rows other than filter clause
to replicate to Central Hub.
Any assistance is highly appreciated!
Database : SQL Server 2000 SP3a
OS: Win2003 (No Service Pack)
Vikas,
you could use an instead-of trigger for this, and divert these rows to
another table.
Alternatively you could use partitioned tables (like federated database
servers but all on one database).
Another alternative is to edit the merge triggers to not record these rows
in the metadata tables.
Rgds,
Paul Ibison
"Vikas Kohli" <VikasKohli@.discussions.microsoft.com> wrote in message
news:ACFC59F9-7CE5-4869-83D3-7CB836E6E273@.microsoft.com...
>I am setting up a SQL Server Database Filter based Merge Replication
>between
> Publisher (on Server Global) & Subscriber (on Server Site) using
> EXCHANGE_TYPE '1' (Data only needs to be merged from Subscriber(Site) to
> Publisher(Global) - Upload). You can consider this as a Central Subscriber
> model with multiple Publications based on Filter "Siteid" push Data to
> Central Subscriber Global. I have created all the Publications in the
> Central
> Publisher Database Global (Normally central subscriber model will have
> Publications on each site but I did wanted to do that due to Maintenance &
> security issues )
> Also I have set up conflict resolver "SQL Server Subscriber always wins
> the
> conflict" since I want my Subscriber changes to override changes done at
> Publisher.
> However, I am facing this issue:
> Even if I add a row at subscriber Site that is not part of Filter (it goes
> into the Publisher Global). I do not want the rows other than filter
> clause
> to replicate to Central Hub.
> Any assistance is highly appreciated!
> Database : SQL Server 2000 SP3a
> OS: Win2003 (No Service Pack)
|||Thanks Paul,
I think the 1st and 2nd options is not feasible at this moment as it
requires a lot of schema changes that will effect almost all the stored procs
in the Application.
I can only try the third option:
I would like to know which merge trigger I have to modify. Is it at the
Publisher or at the Subscriber? I am pasting below the code of Insert
trigger for Merge Replication on an Article "Accrual" for both Subscriber &
Publisher. (for eg. Filter is Siteid=1 for site 1. I do not want rows other
than Siteid 1 to be pushed from Subscriber)
Subscriber Code:
create trigger ins_FD4915FBB5824998BA19B5709C120666 on [dbo].[Accrual] for
insert as
if sessionproperty('replication_agent') = 1 and (select
trigger_nestlevel()) = 1
return
/* Declare variables */
declare @.article_rows_inserted int
select @.article_rows_inserted = count(*) from inserted
declare @.tablenick int, @.nickname int
declare @.lineage varbinary(255), @.colv1 varbinary(2048)
declare @.ccols int, @.retcode smallint, @.version int, @.curversion int,
@.oldmaxversion int
set nocount on
set @.tablenick = 6753038
select @.ccols = 20
set @.lineage = 0x0
set @.retcode = 0
select @.oldmaxversion= maxversion_at_cleanup from dbo.sysmergearticles
where nickname = @.tablenick
execute dbo.sp_MSgetreplnick @.nickname = @.nickname output
if (@.@.error <> 0)
begin
goto FAILURE
end
set @.lineage = { fn UPDATELINEAGE (0x0, @.nickname, 1) }
set @.colv1 = { fn INITCOLVS(@.ccols, @.nickname) }
if (@.@.error <> 0)
begin
goto FAILURE
end
if exists (select ts.rowguid from tsvw_FD4915FBB5824998BA19B5709C120666
ts, inserted i where ts.tablenick = @.tablenick and ts.rowguid = i.rowguidcol)
begin
select @.version = max({fn GETMAXVERSION(lineage)}) from
tsvw_FD4915FBB5824998BA19B5709C120666 where
tablenick = @.tablenick and rowguid in (select rowguidcol from inserted)
if @.version is not null
begin
-- reset lineage and colv to higher version...
set @.curversion = 0
while (@.curversion <= @.version)
begin
set @.lineage = { fn UPDATELINEAGE (@.lineage, @.nickname,
@.oldmaxversion+1) }
set @.curversion = @.curversion + 1
end
if (@.colv1 IS NOT NULL)
set @.colv1 = { fn UPDATECOLVBM(@.colv1, @.nickname, 0x01, 0x00, { fn
GETMAXVERSION(@.lineage) }) }
delete from tsvw_FD4915FBB5824998BA19B5709C120666 where tablenick =
@.tablenick and rowguid in
(select rowguidcol from inserted)
end
end
if (@.article_rows_inserted = 1)
begin
if not exists (select ct.rowguid from
ctsv_FD4915FBB5824998BA19B5709C120666 ct, inserted i where ct.tablenick =
@.tablenick and ct.rowguid = i.rowguidcol)
begin
insert into ctsv_FD4915FBB5824998BA19B5709C120666 (tablenick, rowguid,
lineage, colv1, generation, joinchangegen) select
@.tablenick, rowguidcol, @.lineage, @.colv1, A.gen_cur, A.gen_cur from
inserted,
(select top 1 nickname, gen_cur = isnull(gen_cur, 0) from
dbo.sysmergearticles where nickname = @.tablenick) as A
end
end
else
begin
insert into ctsv_FD4915FBB5824998BA19B5709C120666 (tablenick, rowguid,
lineage, colv1, generation, joinchangegen) select
@.tablenick, rowguidcol, @.lineage, @.colv1, A.gen_cur, A.gen_cur from
inserted,
(select top 1 nickname, gen_cur = isnull(gen_cur, 0) from
dbo.sysmergearticles where nickname = @.tablenick) as A
where rowguidcol not in (select rowguid from
ctsv_FD4915FBB5824998BA19B5709C120666 where tablenick = @.tablenick)
end
if @.@.error <> 0
goto FAILURE
return
FAILURE:
if @.@.trancount > 0
rollback tran
raiserror (20041, 16, -1)
return
Publisher Code
create trigger ins_FD4915FBB5824998BA19B5709C120666 on [dbo].[Accrual] for
insert as
if sessionproperty('replication_agent') = 1 and (select
trigger_nestlevel()) = 1
return
/* Declare variables */
declare @.article_rows_inserted int
select @.article_rows_inserted = count(*) from inserted
declare @.tablenick int, @.nickname int
declare @.lineage varbinary(255), @.colv1 varbinary(2048)
declare @.ccols int, @.retcode smallint, @.version int, @.curversion int,
@.oldmaxversion int
set nocount on
set @.tablenick = 6753038
select @.ccols = 20
set @.lineage = 0x0
set @.retcode = 0
select @.oldmaxversion= maxversion_at_cleanup from dbo.sysmergearticles
where nickname = @.tablenick
execute dbo.sp_MSgetreplnick @.nickname = @.nickname output
if (@.@.error <> 0)
begin
goto FAILURE
end
set @.lineage = { fn UPDATELINEAGE (0x0, @.nickname, 1) }
set @.colv1 = { fn INITCOLVS(@.ccols, @.nickname) }
if (@.@.error <> 0)
begin
goto FAILURE
end
if exists (select ts.rowguid from tsvw_FD4915FBB5824998BA19B5709C120666
ts, inserted i where ts.tablenick = @.tablenick and ts.rowguid = i.rowguidcol)
begin
select @.version = max({fn GETMAXVERSION(lineage)}) from
tsvw_FD4915FBB5824998BA19B5709C120666 where
tablenick = @.tablenick and rowguid in (select rowguidcol from inserted)
if @.version is not null
begin
-- reset lineage and colv to higher version...
set @.curversion = 0
while (@.curversion <= @.version)
begin
set @.lineage = { fn UPDATELINEAGE (@.lineage, @.nickname,
@.oldmaxversion+1) }
set @.curversion = @.curversion + 1
end
if (@.colv1 IS NOT NULL)
set @.colv1 = { fn UPDATECOLVBM(@.colv1, @.nickname, 0x01, 0x00, { fn
GETMAXVERSION(@.lineage) }) }
delete from tsvw_FD4915FBB5824998BA19B5709C120666 where tablenick =
@.tablenick and rowguid in
(select rowguidcol from inserted)
end
end
if (@.article_rows_inserted = 1)
begin
if not exists (select ct.rowguid from
ctsv_FD4915FBB5824998BA19B5709C120666 ct, inserted i where ct.tablenick =
@.tablenick and ct.rowguid = i.rowguidcol)
begin
insert into ctsv_FD4915FBB5824998BA19B5709C120666 (tablenick, rowguid,
lineage, colv1, generation, joinchangegen) select
@.tablenick, rowguidcol, @.lineage, @.colv1, A.gen_cur, A.gen_cur from
inserted,
(select top 1 nickname, gen_cur = isnull(gen_cur, 0) from
dbo.sysmergearticles where nickname = @.tablenick) as A
end
end
else
begin
insert into ctsv_FD4915FBB5824998BA19B5709C120666 (tablenick, rowguid,
lineage, colv1, generation, joinchangegen) select
@.tablenick, rowguidcol, @.lineage, @.colv1, A.gen_cur, A.gen_cur from
inserted,
(select top 1 nickname, gen_cur = isnull(gen_cur, 0) from
dbo.sysmergearticles where nickname = @.tablenick) as A
where rowguidcol not in (select rowguid from
ctsv_FD4915FBB5824998BA19B5709C120666 where tablenick = @.tablenick)
end
if @.@.error <> 0
goto FAILURE
return
FAILURE:
if @.@.trancount > 0
rollback tran
raiserror (20041, 16, -1)
return
Also kindly let me know any problem that you can foresee if I use Central
Subscriber (Globall) using Merge Replication with Ppublisher as all the sites
with Filter Siteid. I am thinking of that option also although it is very
difficult to maintain.
With Regards,
Vikas Kohli
"Paul Ibison" wrote:

> Vikas,
> you could use an instead-of trigger for this, and divert these rows to
> another table.
> Alternatively you could use partitioned tables (like federated database
> servers but all on one database).
> Another alternative is to edit the merge triggers to not record these rows
> in the metadata tables.
> Rgds,
> Paul Ibison
> "Vikas Kohli" <VikasKohli@.discussions.microsoft.com> wrote in message
> news:ACFC59F9-7CE5-4869-83D3-7CB836E6E273@.microsoft.com...
>
>
|||You'll need to edit the subscriber trigger. Don't try to understand all the
code in it - just check at the start if the 'Deleted' table refers to a row
you don't want replicated ie wrap the whole code:
if not exists (select * from deleted where ...)
begin
trigger code
end
This works for single row changes. For multiple row changes, some of which
may violate your filtering, you could try removing the rows from the deleted
table - haven't done this before but might work. If not, you'll have to use
a cursor to iterate through the deleted rows and apply the supplied trigger
code only when the row is ok to be replicated. Remember that this is Very
proprietry solution, and on reinitialization, all this code will disappear.
Rgds,
Paul Ibison
|||Hi Paul,
I have actually used the following code in all the insert & update triggers
for the article in the beginning of trigger:
if exists (select siteid from inserted where siteid=1)
Begin
--Trigger original code--
end
else
Return
For delete trigger, I have used following:
if exists (select siteid from deleted where siteid=1)
Begin
--Trigger original code--
end
else
return
Looks like this code works for even multiple inserts and multiple deletes
Appreciate if you can advise me on this now:
1. Kindly let me know if there could be any issues with the above code.
2. One issue which I can see now is following. Please let me know how to
resolve it.
Although the replication is working fine now but When I run exec
spBrowseMergeChanges 'articlename', I get all the rows listed that were
updated,deleted or inserted at the subscriber. Currently I have to do a
dummyupdate for each rowid which is not possible everytime.
3. Also kindly let me know if I coonfigure a central subscriber in Merge
Replication where the subscriber pulls the data based on Siteid filter, do
you see any issues
With regards,
Vikas Kohli
"Paul Ibison" wrote:

> You'll need to edit the subscriber trigger. Don't try to understand all the
> code in it - just check at the start if the 'Deleted' table refers to a row
> you don't want replicated ie wrap the whole code:
> if not exists (select * from deleted where ...)
> begin
> trigger code
> end
> This works for single row changes. For multiple row changes, some of which
> may violate your filtering, you could try removing the rows from the deleted
> table - haven't done this before but might work. If not, you'll have to use
> a cursor to iterate through the deleted rows and apply the supplied trigger
> code only when the row is ok to be replicated. Remember that this is Very
> proprietry solution, and on reinitialization, all this code will disappear.
> Rgds,
> Paul Ibison
>
>
|||Vikas,
the problem with this approach is that rows where the siteid <> 1 will not
end up logged and replicated. You can make sure this doesn't happen by using
a cursor or by removing the rows from the deleted table (to be tested) and
this'll avoid the need for dummy updates. Am not too sure about (3) - can
you expand a little.
Rgds,
Paul Ibison
|||Hi Paul,
Thanks for reply & Apologies for late reply as the site was lnot loading on
my Browser yesterday.
I will expalain the 3rd option:
Table A & B needs to send data from each site to the Central Hub Server. The
Data that site needs to send is based on filter Siteid.
In my current design, I have a central Publisher that pulls data from Site
using Merge Replication from each site with Filter SiteID. I have used
exchanngetype parameter 1 and Dynamic filter. This approach has only one
problem which we have discussed and could be possibly resiolved by modifying
all the replication Triggers. This option is very good from maintenance point
of view as I have look into only one server and one publication.
The second approach can be Central subscriber as Global Hub. All the sites
can act as Publishers pushing Data through merge replication to the central
Subscriber based on filter condition siteid using exchangetype 2. Appreciate
if you can let me know about this approach. The one problem for me that I can
see is that I have to maintain a lot of publications globally.
With regards,
Vikas Kohli
"Paul Ibison" wrote:

> Vikas,
> the problem with this approach is that rows where the siteid <> 1 will not
> end up logged and replicated. You can make sure this doesn't happen by using
> a cursor or by removing the rows from the deleted table (to be tested) and
> this'll avoid the need for dummy updates. Am not too sure about (3) - can
> you expand a little.
> Rgds,
> Paul Ibison
>
>