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
Best Answer
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!