sql server replication: 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

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

[quoted text, click to view]

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

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

[quoted text, click to view]