Distribution Agent fails with ‘Cannot update identity column ‘OrderID’.’

Chris Skorlinski
Microsoft SQL Escalation Services

I wanted to share this problem which started down a “your data is wrong” path, but ended up with “your customer scripts to create Transactional Replication need to be tweaked”.

Customer reported the following error in Replication Monitor:

Cannot update identity column ‘OrderID’.

Looks like Distribution Agent is trying to apply an UPDATE to an identity column in a Subscriber table. This is not allowed as the identity column would be auto incrementing column whose value is automatically set at the Publisher. We verified the table’s PrimaryKey OrderID column correctly had “Not For Replication” enabled. Digging deeper, we added -OUTPUT to Distribution Agent job to capture Verbose logging then restarted the Agent and examined the log.

OLE DB Subscriber ‘x’: create procedure [dbo].[sp_MSupd_dboOrders]
Agent message code 8102. Cannot update identity column ‘OrderID’.
ErrorId = 40699166, SourceTypeId = 0
ErrorCode = ‘8102’
ErrorText = ‘Cannot update identity column ‘OrderID’.’

We discovered Distribution Agent was not pushing down data, but actually reinitializing the Subscriber with a new snapshot. It is currently trying to create the required Replication generated stored procedures on the Subscriber. However the logic for one particular “create procedure [dbo].[sp_MSupd_dboOrders]” is failing as the Subscriber table, by design contains an identity column. The logic for these Replication generated stored procedure is determined by the publication “schema_option” properties. Wrong schema properties, wrong code. Using SQL Management Studio Replication “Generated Scripts”, found sp_addarticle “Orders”, @schema_option =
0x0000000000030073.

https://blogs.msdn.microsoft.com/repltalk/2017/05/01/script-to-decrypt-the-schema_option-for-a-transactional-replication-article/

**SCHEMA OPTIONS HERE ARE**

———————————————–

0x01 – Generates the object creation script (CREATE TABLE, CREATE PROCEDURE, and so on). This value is the default for stored procedure articles.

0x02 – Generates the stored procedures that propagate changes for the article, if defined.

0x10 – Generates a corresponding clustered index. Even if this option is not set, indexes related to primary keys and unique constraints are generated if they are already defined on a published table.

0x20 – Converts user-defined data types (UDT) to base data types at the Subscriber. This option cannot be used when there is a CHECK or DEFAULT constraint on a UDT column, if a UDT column is part of the primary key, or if a computed column references a UDT column. Not supported for Oracle Publishers.

0x40 – Generates corresponding nonclustered indexes. Even if this option is not set, indexes related to primary keys and unique constraints are generated if they are already defined on a published table.

0x10000 – Replicates CHECK constraints as NOT FOR REPLICATION so that the constraints are not enforced during synchronization

0x20000 – Replicates FOREIGN KEY constraints as NOT FOR REPLICATION so that the constraints are not enforced during synchronization

Missing! 0x04 – Identity columns are scripted using the IDENTITY property.

To correct, select from one of these three options:

—————————————————————————

1. Long term, change user generated “create publication\article” scripts to include ‘0x04 schema_option’ for tables with identify columns
2. Short term, remove table from the publication, then add back using Replication Wizard, selecting default article settings, or
3. sp_changearticle
   ‘x’,
   ‘Orders’,‘schema_option’,
   ‘0x000000000803509F’
   then push down a new Snapshot or Initialize from Backup\Restore