August 6, 2012 at 4:25 am
Good Day,
we experienced one of the dreaded messages using Redgate Transactional Replcation between 2 SQL Server 2008R2 :
SQL Error: Violation of UNIQUE KEY constraint 'xxxx'. Cannot insert duplicate key in object 'dbo.ta_term_cfg'. The duplicate key value is 9999999, 2).
Does that mean we might have to reinitia;ize the subsctiption . Or should we recreate the whole subscription rather ? Any ideas ?
August 6, 2012 at 5:28 am
No need to recreate the whole subscription.
You can also avoid reinitializing the subscription.
You need to identify the row in the subscriber and fix it.
August 6, 2012 at 7:53 am
@suresh - fixing the row at the subscriber would be your easiest method
If you find you must take a new snapshot of the article, make certain you disable the immediate sync option for the publication or you will find you generate a brand-new set of snapshots for every article in your publication. You can prevent this from happening by USE YOURDATABASE
GO
EXEC sp_changepublication @publication = N'PUBLICATION_NAME', @property = N'allow_anonymous', @value = 'false'
GO
EXEC sp_changepublication @publication = 'PUBLICATION_NAME', @property = N'immediate_sync', @value = 'false'
GO
To drop only the bad article (in your case, 'dbo.ta_term_cfg') - you can run
exec sp_dropsubscription @publication=N'PUBLICATION_NAME', @article=N'ta_term_cfg', @subscriber=N'all'
exec sp_droparticle @publication = N'PUBLICATION_NAME', @article = N'ta_term_cfg', @force_invalidate_snapshot = 1
From here you just go to your publication, add the article back in...then restart the snapshot agent job. This will generate a brand-new snapshot for just that particular article
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply