A few days ago I posted a problem I am having with push, one way, transactional replication failing to replicate updates to the subscriber. The identity field is set to Not For Replication on the subscriber and the inserts work, however, my updates are failing with a Cannot Update Identity column. On the publisher, the stored procedure is not updating the identity field so I am at a loss as to why I am getting this error.

Today I had a thought, both the publisher and subscriber are running these databases in 65 compatibility mode. The distribution database is running in 80 compatibiltity mode. Could this be the problem? We are running SQL 2000 standard, but until we can revisit all of our code we are forced to stay in this compatibility mode

For some reason the update Stored Procedure that was created by the replication Wizard creates the following lin

if substring(@bitmap,1,1) & 1 =

which is validating as true, then it tries to specifically update the identity field. If this were to be false I am sure the update would succeed. What is being stored in this @bitmap variable

Now I am really confused

if you are replicating to a sql server using the dbcompatibilty mode of 6.5
you should

1) configure a DSN to the SQL Server
2) enable the subscriber through the DSN
3) configure your publisher to replicate to hetergeneous subscribers
4) create a push subscription to the DSN.
[quoted text, click to view]
that gets called when an update occurs on the table in question on the
publisher. For each part of the IF Statement (if substring(@bitmap,1,1) & 1
= 1) I added an Insert to insert a value into a test table to determine
which part (True or False) was being ran during the replication update. The
false part is being ran, which means it will never try to update the
identity field, however, replication is throwing an error stating it cannot
update the identity field. Why is SQL doing this? Is this a bug??

Denny,
Look at the table on the publisher/subscriber and verify that the identity
column on
the table has the NOT FOR REPLICATION option set. Based on the error, I
would say it does not. Try turning this on and see if that resolves the
problem.
Regards,
Paul

Hi,

I have the same problem. I am sure the Publishing DB table has the Not for replication option, so does the subscribing DB table.

Insertion is replicated, but updates cannot be replicated because of "Identity" column problem.

I manually synchronize the published DB and apply the scripts generated by sp_scriptpublicationcustomprocs to the subscribing DB.

Please help!!

Charles

---
Posted using Wimdows.net NntpNews Component -

Charles,

I can't see the original post so I'm a little blind on this one. It seems
that you are using Transactional replication but what I really need is the
error message and the update that causes the error.

Just a guess but if this update affects a unique/clustered index, it may be
replicated as a delete/insert pair and for singleton updates a trace flag
can be used to avoid the issue
(http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com
:80/support/kb/articles/q302/3/41.ASP&NoWebContent=1).

Regards,
Paul Ibison