July 27, 2001 at 10:44 am
How do we get Merge replication to recognize changes in a source database that has been populated with records via Transaction Replication?
For example:
We are running on SQL Server 2000 on W2K. We are using Transaction replication to replicate from database(A) to database(B). We are then trying to use Merge replication to replicate those records from database(B) into a remote database(C). Transaction replication works fine between (A) and (B) and we can get Snapshots to replicate from (A) to (B) and from (B to (C), but records moved via Transactional replication from (A) to (B) do not replicate via Merge replication from (B) to (C). Merge replication does work when we use Query Analyzer or EM to manually insert records into Database(B) but not when those records are inserted via Transactional replication. We ran a trace and could see that an Insert statement was being generated from (A) to (B) during Transaction replication. We don't understand why Merge replication would see that Insert any differently than an Insert done in EM or QA. Any ideas out there?
Thanks...
Sherri Taylor
Edited by - sherritaylor on 07/27/2001 10:53:17 AM
Sherri Taylor
July 27, 2001 at 5:43 pm
I havent tried that combo, but I cant think of a reason for it not to work. Merge replication basically uses triggers to track the changes - so no matter how you modify your data, it knows its been done. Transaction will typically do its data mods via a stored proc in the subscriber db, which is ok.
Pretty interesting though - I'll try to set up this weekend to test it - unless someone just knows the answer!
Andy
July 30, 2001 at 9:11 pm
Hi Sherri,
Sorry it took so long. I set up three dbs to some testing, first db with a transactional pub to the 2nd, a merge pub on it to the 3rd. Verified your problem, the updates done on the 1st make it to #2, but not #3. If you take a look at the triggers that merge repl creates, you'll see the 'if sessionproperty('replication_agent') line followed by a return. I commented that out (as shown below), I could then get the update to pass through. Not being a merge guru Im not sure what impact this will have as far as updates being passed back from the subscriber..or other problems. But making this change will the problem you asked about!
Have you considered using trans with updating subscriber instead of merge? Or do your business requirements call for merge specifically?
Andy
CREATE trigger upd_34BDC27AFA0E46E7A7C4E749B968BBE6 on [dbo].[authors] FOR UPDATE AS
--if sessionproperty('replication_agent') = 1 and (select trigger_nestlevel()) = 1 -- and master.dbo.fn_isreplmergeagent() = 1
--return
/* Declare variables */
declare @article_rows_updated int
select @article_rows_updated = count(*) from inserted
declare @contents_rows_updated int, @updateerror int
declare @bm varbinary(500), @missingbm varbinary(500), @lineage varbinary(255), @cv varbinary(2048)
declare @tablenick int, @nick-2 int, @ccols int, @partchange int, @joinchange int, @partgen int, @joingen int, @newgen int
declare@partchangebm varbinary(500), @joinchangebm varbinary(500)
set nocount on
set @tablenick = 6886001
select @newgen = gen_cur from sysmergearticles where nickname = @tablenick
if @newgen is null
set @newgen = 0
/* Use intrinsic funtion to set bits for updated columns */
set @bm = columns_updated()
/* only do the map down when needed */
set @missingbm = 0x00
set @partchangebm = 0x00
set @joinchangebm = 0x00
if update([rowguid])
begin
if @@trancount > 0
rollback tran
RAISERROR (20062, 16, -1)
end
/* See if the partition might have changed */
if @partchangebm = 0x0
set @partchange = 0
else
set @partchange= { fn INTERSECTBITMAPS (@bm, @partchangebm) }
/* See if a column used in a join filter changed */
if @joinchangebm = 0x0
set @joinchange = 0
else
set @joinchange= { fn INTERSECTBITMAPS (@bm, @joinchangebm) }
exec dbo.sp_MSgetreplnick @nickname = @nick-2 output
select @ccols = 14
set @lineage = { fn UPDATELINEAGE(0x0, @nick-2) }
set @lineage = { fn UPDATELINEAGE(@lineage, @nick-2) }
set @cv = { fn INITCOLVS(@ccols, @nick-2) }
if (@@error <> 0)
begin
goto FAILURE
end
set @cv = { fn UPDATECOLVBM(@cv, @nick-2, @bm, @missingbm) }
if @joinchange = 1
begin
set @joingen = @newgen
set @partgen = @newgen
end
else if @partchange = 1
begin
set @partgen = @newgen
set @joingen = NULL
end
else
begin
set @partgen = NULL
set @joingen = NULL
end
update ctsv_34BDC27AFA0E46E7A7C4E749B968BBE6
set lineage = { fn UPDATELINEAGE(lineage, @nick-2) },
generation = @newgen,
joinchangegen = case when (@joinchange = 1) then @newgen else joinchangegen end,
partchangegen = case when (@partchange = 1) then @newgen else partchangegen end,
colv1 = { fn UPDATECOLVBM(colv1, @nick-2, @bm, @missingbm) }
FROM inserted as I JOIN ctsv_34BDC27AFA0E46E7A7C4E749B968BBE6 as V
ON (I.rowguidcol=V.rowguid)
and tablenick = @tablenick
select @updateerror = @@error, @contents_rows_updated = @@rowcount
if @article_rows_updated <> @contents_rows_updated
begin
insert into ctsv_34BDC27AFA0E46E7A7C4E749B968BBE6 (tablenick, rowguid, lineage, colv1, generation, partchangegen, joinchangegen)
select @tablenick, rowguidcol, @lineage, @cv, @newgen, @partgen, @joingen from inserted where
rowguidcol not in (select rowguid from ctsv_34BDC27AFA0E46E7A7C4E749B968BBE6 where tablenick = @tablenick)
if @@error <> 0
GOTO FAILURE
end
return
FAILURE:
if @@trancount > 0
rollback tran
raiserror (20041, 16, -1)
return
August 27, 2001 at 1:41 pm
It does not populate because merge is managed by a set of triggers behind the replicated tables. These triggers are coded to ignore anything that is done using a replication agent. This means that the merge triggers will fire, but will not do anything. In order to make it work, you will need to modify all of the merge triggers to remove the block of code that exists when a change is done using a replication agent. Be EXTREMELY careful when you do this, you can introduce endless loops by doing this quite easily. Make CERTAIN you leave the fn_isreplmergeagent in the trigger, but disable the sessionproperty. Make sure you TEST on a clean system FIRST.
Mike
SQL Server MVP
Mike
SQL Server MVP
http://www.mssqlserver.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply