Distributor Agent Error using transactional replication

Distributor Agent Error using transactional replication

Post 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.

 
 
 

Distributor Agent Error using transactional replication

Post 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.


 
 
 

1. Transactional replication using remote Distributor

I've configured transactional replication on a Publisher using a
remote Distributor. Now I'm faced with a problem where a remote
subscriber (placed on a different network) needs to acces my
publication.

Currently the Publisher and the Subscriber have no knowledge of each
other - and I would like to keep it that way for security reasons. The
Subcriber can see the Distributor which in turn can see both the
Publisher and the Subscriber.

I have read somewhere on this newsgroup that when using transactional
replication the Subscriber does not need to access the Publisher. But
how is it then possible to let it subscribe to a publication on a
server it can't access. Or on the Publisher grant the Subscriber
access to the publication when this can't see the Subscriber?

Answers will be greatly appreciated...

Claus Thorning Madsen
Web-developer

SIX danmark A/S
http://www.six.dk

2. parameterized Where clause

3. Snapshop agent Error in Transactional Replication

4. correct pg_hba.conf setup for remote connections.

5. Transactional replication Log Reader Agent Error

6. data_protection message in DB2DIAG.LOG

7. Transactional replication - LogReader Agent Error

8. US-NH-SR. DEVELOPERS

9. Snapshot Agent error in Transactional replication

10. How to start/stop replication agent for transactional replication via Transact SQL

11. Transactional replication- distributor removes FK constraints

12. Transactional Replication failure: License and Log Reader Agent

13. Programmatically Restart Distribution Agent in Transactional Replication