Why does the 'Arithmetic overflow error converting IDENTITY to data type int' occur during Pro2 replication, and how can it be resolved?

Pro2 replication fails with an arithmetic overflow error. This typically happens when a record is inserted into a target SQL Server table, which then fires a trigger that attempts to insert data into a secondary table (such as an audit or history table) where an INT IDENTITY column has reached its maximum value of 2,147,483,647.

The root cause is a SQL Server INT IDENTITY column reaching its upper limit. In this specific case, the Pro2 replication process triggered a secondary insert via a database trigger. Because the secondary table's identity column was defined as an INT rather than a BIGINT, it could no longer increment, resulting in the overflow error and stopping the replication process.

In this scenario:

1. Pro2 attempts to replicate a record to Table A.

2. An INSERT trigger on Table A attempts to write data to Table B.

3. Table B uses an INT data type for its IDENTITY column which has reached its limit, causing SQL Server to reject the transaction and Pro2 replication to halt.

Identify the affected table: Review the triggers on the table where Pro2 is reporting the error to find which secondary tables are being updated.

Audit Downstream Tables: Identify which secondary tables the trigger writes to.

Convert to BIGINT: Modify the affected column in the secondary SQL Server table to a BIGINT data type to accommodate larger values.

Restart Replication: Once the data type is updated, restart the Pro2 replication threads to clear the queue.

If a schema change cannot be performed immediately, the DBA may need to temporarily disable the trigger or reseed the identity value.

Currently, Pro2 logs provide the error returned by the SQL driver. If the error occurs within a SQL trigger, the log may not explicitly name the secondary table where the overflow happened. Users must manually inspect the triggers on the table being replicated.