I've had to start over from scratch on one of my transactional replication with updatable subscribers. Since doing this, I have been getting an error whenever trying to insert a record into one of the tables on the subscriber side: "Violation of PRIMARY KEY constraint 'PK_TABLE1'. Cannot insert duplicate key in object 'dbo.TABLE1'. The statement has been terminated."
The identity range constraints that are automatically created when setting up replication are exclusive of each other.
Publisher: ([ID]>(235671) AND [ID]<(250000))
Subscriber: ([ID]>(250000) AND [ID]<(251000))
I've tried dropping the subscription and the publication, but I'm still getting the same message.
Can anyone spread some light on the issue I am having? How do I fix this?
I got around this on the subscriber side by starting from a brand new database and setting replication up. Everything was working fine for a couple days. This morning I started getting this same error on the publisher side. "Violation of PRIMARY KEY constraint 'PK_TABLE1'. Cannot insert duplicate key in object 'dbo.TABLE1'. The statement has been terminated." However, the subscriber side was working without issue.
I didn't notice this the last time, but when I setup replication, the identity range that it picked up was not empty, there were already records in that range. In this morning's example, the ID range for the table having issues was this: ([ID]>(894037) AND [ID]<(896000)). However, there were existing records inside that range (from before replication was setup this time) starting at ID = 895001 and going to 895109. The last successful record inserted this morning was 894990, but I have a process that tries to insert 15 records, but it couldn't because 895001 was already taken. All I had to do was reseed this table on the publisher side to the next available ID (895110) and my applications started working again.
This has brought up a few questions...
1. By running the reseed, will this cause any problems with the automatic identity range management when it's time to get a new range?
2. When the replication was initially setup, why did it not get a completely empty range? Why did it choose a range that already had records in it?
Thanks in advance.
1. Can you try sp_adjustpublisheridentityrange instead?
2. not sure, did you set up via procs or UI? if via procs, did you use parameter @identityrangemanagement = 'auto'? Otherwise not sure, you may want to check what IDENT_CURRENT( ) returns prior to setting up the range.
1. I tried to run sp_adjustpublisheridentityrange, but I was getting an error "Invalid object name 'dbo.sysmergepublications'". I had tried to setup a merge replication, but couldn't get it to work, so I just disabled it. Ever since then, I've been getting that error message when doing certain things. Finally, I found that I needed to run: sp_removedbreplication 'database1', 'merge'. This morning, I did cross the threshold of ID's in the first range, and it did successfully adjust the range like it should have.
2. I set it up with the UI to automatically manage the identity range. Is there an option to pick the initial range? I'd hate to have to double check the identity range for every table I setup to be replicated. I would think that the replication setup process would have picked a range that was actually empty.