Very Computer

by **David Lightman Roble** » Tue, 25 Sep 2001 20:55:13

I have recently installed two Win2K servers running SQLServer2000 and they
replicate each other using transactional replication with updatable
subscriptions (SERVER01 is the publisher and distributor, and SERVER02 is
the subscriber). I have published several publications all of them with the
same characteristics (updatable transactional replication). All but one work
fine. They can be modified either at the publisher or the subscriber and the
change is replicated to the other side perfectly. However I get a red-cross
on one of the Distributor Agents (there is one agent for each publication,
but there is just one showing the error status). When I doubleclick on it I
get the following information:

Publisher:
SERVER01

Agent:
SERVER01-mydb-Clients-SERVER02-24

Publication:
Clients

Subscription:
SERVER02:mydb

Last command:
{CALL sp_MSdel_TClients (862)}
Transaction sequence number and command ID of last execution batch are
0x00006A22000002BC000100000000 and 1.

Error message:
DELETE statement conflicted with COLUMN REFERENCE constraint
'FK_TClientAdresses_TClients'. The conflict occurred in database 'mydb',
table 'TClientAdresses', column 'ClientCode'.

Error details:
DELETE statement conflicted with COLUMN REFERENCE constraint
'FK_TClientAdresses_TClients'. The conflict occurred in database 'mydb',
table 'TClientAdresses', column 'ClientCode'.
(Source: SERVER02 (Data source); Error number: 547)
----------------------------------------------------------------------------
-----------------------------------
The row was not found at the Subscriber when applying the replicated
command.
(Source: SERVER02 (Data source); Error number: 20598)
----------------------------------------------------------------------------
-----------------------------------

There is a constraint 'FK_TClientAdresses_TClients' at the publisher to
enforce the referential integrity between Clients and ClientAdresses. There
are lots of other similar contraints in the other publications too, but it
seems there is something wrong with this in particular.

I have tried to remove the subscription, then the publication and then
recreate the publication and push again the subscription but the error
arises after a while. I have also tried to delete the constraint and then
recreate it again using the option 'Check existing data on creation'. None
of these two ideas solved the problem at all.

What can be going wrong? Any ideas?

Thanks in advance for your help.

by **TSVK** » Wed, 03 Oct 2001 09:31:32

Given that you have updatable subscriptions, I am wondering you are having
some loopback problems.

subscriber which originated it. Also see if you have constraints set to
"NOT FOR REPLICATION". This may help
But the obvious first step is to look at the delete that is causing the
conflict. Try running it manually and see if it works.

Quote:> I have recently installed two Win2K servers running SQLServer2000 and they
> replicate each other using transactional replication with updatable
> subscriptions (SERVER01 is the publisher and distributor, and SERVER02 is
> the subscriber). I have published several publications all of them with
the
> same characteristics (updatable transactional replication). All but one
work
> fine. They can be modified either at the publisher or the subscriber and
the
> change is replicated to the other side perfectly. However I get a
red-cross
> on one of the Distributor Agents (there is one agent for each publication,
> but there is just one showing the error status). When I doubleclick on it
I
> get the following information:

> Publisher:
> SERVER01

> Agent:
> SERVER01-mydb-Clients-SERVER02-24

> Publication:
> Clients

> Subscription:
> SERVER02:mydb

> Last command:
> {CALL sp_MSdel_TClients (862)}
> Transaction sequence number and command ID of last execution batch are
> 0x00006A22000002BC000100000000 and 1.

> Error message:
> DELETE statement conflicted with COLUMN REFERENCE constraint
> 'FK_TClientAdresses_TClients'. The conflict occurred in database 'mydb',
> table 'TClientAdresses', column 'ClientCode'.

> Error details:
> DELETE statement conflicted with COLUMN REFERENCE constraint
> 'FK_TClientAdresses_TClients'. The conflict occurred in database 'mydb',
> table 'TClientAdresses', column 'ClientCode'.
> (Source: SERVER02 (Data source); Error number: 547)
> --------------------------------------------------------------------------
--
> -----------------------------------
> The row was not found at the Subscriber when applying the replicated
> command.
> (Source: SERVER02 (Data source); Error number: 20598)
> --------------------------------------------------------------------------
--
> -----------------------------------

> There is a constraint 'FK_TClientAdresses_TClients' at the publisher to
> enforce the referential integrity between Clients and ClientAdresses.
There
> are lots of other similar contraints in the other publications too, but it
> seems there is something wrong with this in particular.

> I have tried to remove the subscription, then the publication and then
> recreate the publication and push again the subscription but the error
> arises after a while. I have also tried to delete the constraint and then
> recreate it again using the option 'Check existing data on creation'. None
> of these two ideas solved the problem at all.

> What can be going wrong? Any ideas?

> Thanks in advance for your help.

Very Computer

Distributor Agent Error using transactional replication

Distributor Agent Error using transactional replication

Distributor Agent Error using transactional replication