QUESTION POSED ON: 12 March 2004 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.
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, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Search and Browse the Expert Answer Center Search and browse more than 25,000 question and
answer pairs from more than 250 TechTarget industry experts.
TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of technology-specific websites, events and online magazines.
