September 22, 2015 at 9:21 am
I have a transactional publication done between two databases on different Virtual machines connected with a network. I noticed that for some reason Every time replication is happening (which is every 2,5 seconds) my data is deleted on the target DB (subscriber) then re inserted.
this should not happen, old data should remain then only the new data should be inserted.
September 23, 2015 at 3:55 am
Does this apply?
If any column that is part of a unique constraint is updated, then SQL Server implements the update as a "deferred update", which means as a pair of DELETE/INSERT operations. This "deferred update" causes replication to send a pair of DELETE/INSERT statements to the subscribers. There are also other situations that might cause a deferred update. Therefore, any business logic that you implement in your UPDATE triggers or custom stored procedures at the Subscriber should also be included in the DELETE/INSERT triggers or custom stored procedures.
September 23, 2015 at 7:02 am
if yes how should I avoid this ?
September 30, 2015 at 6:43 am
I found the solution. The problem was that replication have a history table where it puts foreign keys associated with replication. I found in this table an old foreign key which dos not exist anymore in our database. I deleted and all worked fine.
delete dbo.MSsavedforeignkeys where constraint_name = N'FK_ID'
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply