Problem solve Get help with specific problems with your technologies, process and projects.

Error in "Not for replication" database revisited

This question was posed originally on March 12 of this year, and I am having the exact same issue. Your answer was unsatisfactory due to the question being vague, so I will attempt post the question again using more specific verbiage.

We are using transactional replication -- push subscription. The publisher and distributor run on the same instance of SQL Server (8.00.194) with SP3 (build 2195) installed (no errors during installation of any service packs). The published database has a table with an idenity column, set at the subscriber as "Not For Replication". The article defaults are set to keep the table data, rather than dropping. Inserts are sent to the subscriber with no problem. Updates at the publisher cause replication to error, reporting "Cannot update identity column 'column name' (Error #8102 - trying to alter the value of an identity column in SET portion of the UPDATE statement). Why is this happening?

It is happening, because the subscriber isn't supposed to have an identity column in transactional replication. The identity column is only useful if you are inserting data into the table. Transactional replication assumes a logically read only subscriber and hence it does not expect to you have constructs which would only be used if you were directly modifying data. Therefore, the processes which issue the insert against the subscriber are not coded to expect an identity column to exist. The true solution is to remove the identity column which shouldn't be on the subscriber. The workaround is to write a custom stored procedure for the insert which issues a "set identity insert on" statement.


For More Information

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.