When IDENTITY_INSERT is set to OFF, an explicit value cannot be inserted for the Identity column in table ' XXX '.

When creating transactional replication, it is not always necessary to initialize with a snapshot, but rather to use a backup to restore the initialization. When you copy a table with an identity column (that is, an identity's self-increment column), a copy that is initialized with a backup is often an error: When IDENTITY_INSERT is set to OFF, an explicit value cannot be inserted for the Identity column in table ' XXX '. This error is when creating the table, there is a ' identity specification ' in the ' Not FOR replication ' default to ' No ', when the Publisher insert data, right-click Table, select design Options,

When initialized with a backup restore, this table on the subscriber is also ' no ', so it will be an error. There are several ways to modify this replication without deleting it:

1. The simplest is to say that this option on the Subscriber is changed to ' yes ';

2. Since this option is ' no ' here, then in the creation of this table can be added to the identity column: not FOR replication, but now that the table and for this table is configured for replication, you can execute the following code at the Subscriber,

Use [Database]
Go
Altertable[tablename]altercolumn[  ColumnName]addnot forreplication

3. The third method requires a certain understanding of the principle of replication, and replication will only pass the data changes caused by Update,delete and insert operations on the replicated table, and at the Subscriber, three stored procedures will be created for this table, respectively Dbo.sp_msupd_ The dbo table name, the dbo.sp_msdel_dbo table name, the dbo.sp_msins_dbo table name, and the three stored procedures to modify the subscription table. So because the identity exists, the IDENTITY_INSERT option is off, and each insertion needs to be identity_insert set to on to insert, we can directly insert this stored procedure (dbo.sp_msins_ DBO table name), and with the following sentence, you can insert the data automatically.

Set [ table name ]  on

When IDENTITY_INSERT is set to OFF, an explicit value cannot be inserted for the Identity column in table ' XXX '.