Marking Identity Columns as “Not For Replication” in existing Publication

Chris Skorlinski
Microsoft SQL Server Escalation Services

We encounter a problem today with a Distribution Agent failing with “Row Not Found” when pushing an UPDATE to a Subscriber.  Reaching the problem we discovered the Subscriber was created using a Backup\Restore from the Publisher and had Identity Columns.  The Identity Columns had “Not For Replication” set to NO, which flags the Distribution Agent to “renumber” (i.e. reseed) data as it is Replication to the Subscriber.

For example:  Row 100 inserted at Publisher,  but is renumbered by the Distribution Agent to row 20 at the Subscriber.  Later same row 100 is edited on the Publisher.  The Distribution Agent looks for but is unable to find row 100 and fails with “row not found”.  Instead we want the Distribution Agent to use the same value from the Publisher.  For this we need to enable the “Not For Replication” setting on the Identity Column on the Publisher table.

If you try and change the Publisher table design while data exists you get error which prevents saving changes.  To work around this problem you can use the stored procedure below to change the “Not For Replication” setting for all tables in the database then reinitialize the subscriber.

Change identity setting for all tables in the database:

EXEC sp_msforeachtable @command1 = '
declare @int int
set @int =object_id("?")
EXEC sys.sp_identitycolumnforreplication @int, 1'