Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are sporadically getting this error:

Explicit value must be specified for identity column

in our replication environment. I have checked the transaction command that is reporting the error and it contains three inserts for three different tables. All three inserts DO HAVE the identity value in them. If I remove the primary keys from the tables and allow nulls then three records with NULL in what was the primary key field are inserted AS WELL AS three records with the correct primary key values (as seen using sp_browsereplcmds).

I remove the null value records and re-enable the primary keys, identity and not for replication flag and replication continues as normal.

I'm really stumped and can't figure out what is going on, any suggestions appreciated.

Currently I'm considering modifying the automatically created MSSPs so that if it finds a null in what would be the primary key value it ignores it, but this shouldn't be necessary.

Cheers

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

After much trial and error we think the answer was and has always been triggers! Despite being disabled in the subscriber they kept on being re-enabled which was causing a second insert command to run for affected replicated commands.

Basically check your triggers!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.