I had replication errors recently with some subscriptions that were set up with replication support only (manually created and populated destination tables). These are transactional push subscriptions that are not updateable (i.e. they are one way only from the source to the destinations and the destinations must keep the same keys as the source).

The error was visible in the distribution.dbo.MSrepl_errors table:

Cannot insert explicit value for identity column in table 'TABLE_NAME' when IDENTITY_INSERT is set to OFF.

The cause of this is that in this style of replication most DBAs will either create the destination tables with a plain column instead of an identity column, or they will create the column with the NOT FOR REPLICATION flag. I had trouble finding explicitly what that flag would do in my scenario, but now I can boil it down to this:

  • The flag doesn't mean anything on the publisher.
  • The flag means everything on the subscriber.

  • If it's set on a subscriber it means the replication agent can insert the identity column without doing a Set Identity_Insert command (which it would never do).

    The best way I can think of remembering this is NFR means "identity column inserts are NOT checked FOR the REPLICATION agent".

Turning off the identity columns was not an option because you can't do that in SQL Server 2005+. I couldn't just create and populate a new column either because of the dependencies, and it would also change the column ordering which would cause validation to fail. Nor could I recreate everything without turning off replication and losing all of the data in the middle and doing a lengthy initial sync again.

So NOT FOR REPLICATION is exactly what was needed. You can identify and update the tables to have this flag, with a command like this, on each of the subscribers:

Declare @TableID Int = (
	Select	Top 1 sys.sysobjects.id As Table_ID
	From	sys.sysobjects
	Join	sys.syscolumns
	On	syscolumns.id = sysobjects.id
	Where	sys.sysobjects.name = 'TABLE_NAME'
	And	(sys.syscolumns.status & 0x88) = 0x80 -- 0x80 = Identity, 0x08 = Not For Replication
	Order By 1
	)

IF @TableID IS NOT NULL
EXEC    sys.sp_identitycolumnforreplication @TableID, 1

After doing this and starting up the replication agents again, everything continued smoothly.