SQL2000: Unable to insert into repliacted table
- From: "Des Norton" <des@xxxxxxxxxxxxxxxxx>
- Date: Thu, 27 Mar 2008 15:15:50 +0200
Hi NG
We have a Merge replication (SQL2000). For no apparent reason, inserts into
the replicated tables have suddenly stopped working. I have done a DBCC
REINDEX on all the tables/indexes, with no change. Tables contain approx 12
million records. All PK and FK fields are uniqueidentifiers.
The insert statements are in the following format. (dbo.Table2 is the
replicated table)
insert dbo.Table2 ([RecordID], [Field2], [Field3])
select t1.[RecordID], t1.[Field2], t1.[Field3]
from dbo.Table1 t1
left join dbo.Table2 t2 on t2.[RecordID]=t1.[RecordID]
where t2.[RecordID] is null
I have set up dummy data so that the select only returns 1 record. This is
done in under 1 second. If I run the above statement in QueryAnalyser, I
give up and cancel the insert after 3 hours.
However, a modified (2 phase) insert succeeds (± 40 seconds).
select t1.[RecordID], t1.[Field2], t1.[Field3]
into #Tmp
from dbo.Table1 t1
left join dbo.Table2 t2 on t2.[RecordID]=t1.[RecordID]
where t2.[RecordID] is null
insert dbo.Table2 ([RecordID], [Field2], [Field3])
select [RecordID], [Field2], [Field3]
from #Tmp
Any insights into the issue would be appreciated.
Des Norton
.
- Prev by Date: Re: SQL 2005 Peer-To-Peer - How to configure failover (If possible
- Next by Date: Re: SQL 2005 Peer-To-Peer - How to configure failover (If possible)
- Previous by thread: SQL 2005 Peer-To-Peer - How to configure failover (If possible)
- Next by thread: SQL 2000 bi-directional replication
- Index(es):
Relevant Pages
|
