Inserting into table with an Identity column while replication causes error in SQL Server

user_3053643 0
Viewed 46988

I have a table A_tbl in my database. I have created a trigger on A_tbl to capture inserted records. Trigger is inserting records in my queue table B_tbl. This table has an Identity column with property "Not for replication" as 1.

  • A_tbl (Id, name, value) with Id as the primary key
  • B_tbl (uniqueId, Id) with uniqueId as Identity column

Trigger code doing this:

Insert into B_tbl (Id)
    select i.Id from inserted

Now my table 'B' is replicated to another DB Server, now when I'm inserting into table 'A' it is causing this error:

Explicit value must be specified for identity column in table 'B_tbl' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column. (Source: MSSQLServer, Error number: 545)

Please help me resolve this issue.

database-replication ssms sql-server sql database
4 Answers

Just to add one possible gotcha for people who like me did everything by the book and still got the message.

Check for triggers on insert. They may be attempting to create more rows while executing code that does not have the explicit identity column values.

If it's the case, disable the trigger (or drop and recreate it).

user_2791740
Related