January 30, 2009 at 5:19 am
Hi All
We have a merge replication set up accross two sites. One of the tables that is replicated contains around 15 million rows.
Once a week around 10 million of these rows are delete and replaced with a similar number.
When this table is in the publication the insert fails as it runs out of new identities.
To overcome this problem i have have tried to reseed the identities of the table before the insert happens.
I have got this working for the publisher (ie. the first id will be 1 rather then the last number inserted) But the ID changes are not being carried over to the subscriber.
Here is the code i have been testing with.
CREATE TABLE TmpTable ( Data1 NVARCHAR(50),
Data2 NVARCHAR(50),
RowGuidNVARCHAR(50))
Go
INSERT INTO TmpTable ( Data1, Data2, RowGuid )
SELECT Data1,
Data2,
RowGuid
FROM dbo.Identity_Test
Go
DELETE FROM dbo.Identity_Test
Go
DBCC CHECKIDENT (Identity_Test,RESEED,1)
Go
INSERT INTO dbo.Identity_Test ( Data1, Data2,RowGuid )
SELECT Data1,
Data2,
RowGuid
FROM TmpTable
Go
As you an see the values are being reinserted so the table should see them as new values and give them new IDs (which it does) and the replication agent should see them as new values in the publisher and create them with the same ID values in the subscriber, which it does not.
Any ideas?
January 30, 2009 at 5:59 am
Actually, it was my mistake. It was complaining because i was inserting the rowguid. Take that out and it all works fine.:D
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply