Jon Gallant

Solution: A row insert at ... could not be propagated to ... This failure can be caused by a constraint violation. Cannot insert explicit value for identity column in table ... when IDENTITY_INSERT is set to OFF.

1 min read
devsql

I just got this error when doing Merge Replication on a SQL Server 2005 environment:

Terminal window
A row insert at ... could not be propagated to ... This failure can be caused by a constraint violation. Cannot insert explicit value for identity column in table ... when IDENTITY_INSERT is set to OFF.


```python


The error appeared when I tried to replicate a record inserted at the Publisher to the Subscriber. The Subscribers were built from backups, not snapshots. Apparently when you do that the NOT FOR REPLICATION flag is automatically set to No. In order for the Publisher->Subscriber scenario to work, that flag needs to be set to Yes.


You could manually do that for all tables with IDENTITY columns, or you could run this script.


```sql


EXEC sp_msforeachtable @command1 = '
declare @int int


set @int = object_id("?")


EXEC sys.sp_identitycolumnforreplication @int, 1


PRINT "?"

That will set all NOT FOR REPLICATION flags to Yes and will print out the list of tables it looped through.

You may also get this error if the same flag is set to No for Triggers or other objects.

Hope this saves you some time

Jon

Share:
Share on X