Friday, February 17, 2012 11:21 AMI have set up a transactional replication on my master tables. This master table’s in-turn populates data into history table using DML triggers. I need to replicate only master tables to my subscriber. What I can make out is that the triggers are automatically replicated when the table is replicated but identity column is not replicated as it is marked "Not for replication".
The replication fails with an error saying 'Cannot insert explicit value for identity column in table 'XX' when IDENTITY_INSERT is set to OFF' and neither master nor history table on subscriber get populated.
Friday, February 17, 2012 4:23 PMModeratorMake the triggers on teh subscriber not for replication and ensure that the identity column on the subscriber is marked as not for replication. This should take care of the problem.
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
- Marked As Answer by Maggie LuoMicrosoft Contingent Staff, Moderator Tuesday, February 28, 2012 8:13 AM