Transactional Bi-Directional Replication failing.
- From: InvestorTrade <shija03@xxxxxxxxx>
- Date: Mon, 19 Nov 2007 11:53:33 -0800 (PST)
Hi,
I've been trying to implement the Transactional Bi-Directional
Replication as per: http://support.microsoft.com/kb/820675, and I
continue getting the following error whenever I try to update either
on the subscriber or the publisher:
UPDATE mytest SET mydata='hello there' WHERE id='101'
I continue receiving the following error:
{CALL sp_MSupd_mytest (NULL,'See how it changed',46,0x02)}
Transaction sequence number and command ID of last execution batch are
0x00000B9F00004CA9000500000000 and 1.
and
"Cannot update identity column 'id'."
My Configuration is as follows:
Publisher Server A - Subscriber Server B
Publisher Server B - Subscriber Server A
Table definitions:
Server A:
CREATE TABLE [dbo].[mytest] (
[id] [int] IDENTITY (1, 2) NOT FOR REPLICATION NOT NULL ,
[mydata] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
Server B:
CREATE TABLE [dbo].[mytest] (
[id] [int] IDENTITY (2, 2) NOT FOR REPLICATION NOT NULL ,
[mydata] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
Server A and Server B as subscribers have the @loopback_detection =
true
Why is the MSupd_mytest trying to do an update on the Primary key? Is
there a way via configuration to make this behavior work properly? If
not, do I need to alter the MSupd_mytest procedure to be different
(how and to what?).
sp_MSupd_mytest looks as follows:
ALTER procedure "sp_MSupd_mytest"
@c1 int,@c2 varchar(50),@pkc1 int
,@bitmap binary(1)
as
if substring(@bitmap,1,1) & 1 = 1
begin
update "mytest" set
"id" = case substring(@bitmap,1,1) & 1 when 1 then @c1 else "id" end
,"mydata" = case substring(@bitmap,1,1) & 2 when 2 then @c2 else
"mydata" end
where "id" = @pkc1
if @@rowcount = 0
if @@microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update "mytest" set
"mydata" = case substring(@bitmap,1,1) & 2 when 2 then @c2 else
"mydata" end
where "id" = @pkc1
if @@rowcount = 0
if @@microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
I can not use Merger replication nor any other bidirectional
replication that involves altering tables with a GUID.
If the current test works, I'll have to make replication work for 300
Articles.
Any ideas?
.
- Follow-Ups:
- Re: Transactional Bi-Directional Replication failing.
- From: Hilary Cotter
- Re: Transactional Bi-Directional Replication failing.
- From: InvestorTrade
- Re: Transactional Bi-Directional Replication failing.
- Prev by Date: Help: Replication stops with no error or warnings!!!
- Next by Date: Re: Transactional Bi-Directional Replication failing.
- Previous by thread: Help: Replication stops with no error or warnings!!!
- Next by thread: Re: Transactional Bi-Directional Replication failing.
- Index(es):
Relevant Pages
|
