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

Error in "Not for replication" database

We are using transactional replication - push subscription. The subscriber database is set as "Not For Replication". When a row is inserted at the publisher, the change is sent to the subscriber with no problem. When a column is updated in the publisher, I get an error: "Cannot update identity column 'column name' (Error #8102 - trying to alter the value of an identity column in SET portion of the UPDATE statement). I'm NOT trying to update the identity column, and this column did NOT change, but when I look at the last command issued by the replication agent, it's calling sp_MSupd_tablename. When I look at the stored procedure, there is a variable @bitmap binary (2) being passed in (the value is 0x0800). There is an if statement "if substring(@bitmap,1,1) & 1=1" - if this returns true, then the procedure attempts to change the primary key field. This is a stored Microsoft procedure that is automatically created with the distribution services. This field is a primary key field in both the publisher and subscriber. I don't understand why the procedure would even be attempting to change it. I'm hoping you can give me a little more detail on this and possibly a solution. Our thought is to remove the "if" portion of the procedure and the statements following it and just leave the "else" statements remaining, since the primary key value should never be changed anyway. Do you see any issues with doing that?

I don't understand what setting a database to not for replication is. You can set identity columns, foreign keys, and triggers to use the not for replication option. It isn't something that can be done at a database level. I'd also need to know what version of SQL Server and what service pack level you are running and if any errors were generated during the application of a service pack.


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.