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
- Dozens more answers to tough SQL Server questions from Michael Hotek are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.