1

I have an Transactional replication set up form Server A to Server B. I have a table which has an Identity column as a primary key. In my publisher ,for the table tb1,the the identity column,Id1 was set as "Not for Replication" as Yes.

I used to get the error message "Cannot insert explicit value for idntity column in table tb1 when IDENTITY_INSERT is set to OFF" But when i checked my subscriber ,i saw that the same column as set as as "Not for Replication" as No. So i used the below script to change the setting in my subscriber tb1.

alter table [dbo].[tb1] alter column [Id1] add not for replication;

And the "Not for Replicaiton" is set as "Yes" in my subscriber too.

So what ever Id1 value in the source should be replicated to the subscriber table as well.

But now i get the below error message "Explicit value must be specified for identity column in table tb1 when IDENTOTY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identtity column"

Seems like the explcit identity value in Id1 is not being passed from publisher.

Any suggestions on this.

Improve this question
3
  • 1
    See my answer How do Identity Columns get Replicated? - You have to do this on both publisher and subscriber.
    – Kin Shah
    Aug 5, 2019 at 3:08
  • @KinShah : I checked your answer and unfortunately i have to keep NOT FOR REPLICATION as yes to have the Identity values same in Publisher and in Subscriber.My Publisher table has NOT FOR REPLICATION as YES and subscriber table was set to NO ,but i changed it to YES.Even after i was getting the same error.After creating a separate publisher/subscriber for that table i was able to get it synched,but i still get the error message.
    – user9516827
    Aug 6, 2019 at 14:34
  • Please ignore my comment.I found out that a trigger was present and it was causing the error.
    – user9516827
    Aug 6, 2019 at 15:16

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.