Error 8102:cannot update identity column

I set up replication on my DB between two server. The Publisher will not update tables on the Subscriber that use an identity column and I get an error"8102:cannot update identity column". I have looked around and can't seem to locate any solid fixes. If I remove the identity column from the subscribers tables the package works, but I am not able to use the DB on the subscriber because I need the Identity columns for my app to function properly. My Publisher and Subscriber are both running SQL Server 2000 Standard Edition SP3.

Thanks.Check out this MSDN Article:Replication: Managing Identity Values.

Terri|||Thanks for the reply, I already read this article and it solves the problem but creates another. The article does describe how to increment the Identity column on the subscriber, but it will not match the value on the publisher because you must set the ID column ranges in seperate blocks. Server P 1-1000 Server S 1001-2000 When 1 is used on P, it insert 1001 on S.

Here's what I'm doing and maybe there is another way to do it.

I have two servers running my application, 1 exposed to the Internet for client access and one for my Intranet. I need an exact replica of my database on the Internet server that is being updated periodically from my Intranet server. I am installing clustered servers soon but I need to have an interim solution in place until then. The publically exposed server is also to act as a backup in the event that my Intranet server goes down.

The database is currently being replicated between the servers, but the Identity columns on the subscriber are not set, so if we had to switch to it I would have to define the ID columns in my tables for my application to work properly.

Would it be okay to point my Internet servers WEB.CONFIG data connection string to the Intranet server and just allow the replication to continue as is, and if I do have an outage direct my Intranet traffic to the Internet server, change the conn string in the WEB.CONFIG and define the ID columns?

Thanks in advance.|||Is your Internet server then serving as a warm standby? Is it then basically a read-only version of the database on your Intranet server??

If so, I can telll you that we usetransaction log shipping to keep a warm standby of our database on a different server.

Otherwise, I'm sorry, replication is out of my league. We had used replication for a short while until we found out that it did not meet our needs (due in part to the IDENTITY column situation). With some research and effort we might have gotten replication to work, but log shipping was exactly right for us.

Terri
PS, if you haven't seen it, you might consider this article:Architecting Replication with Identity Columns
|||Transaction logs would work if I had the Enterprise version of SQL, but I won't have it for a few more months, at which point I am installing clustered servers. I think for the time being I am going to go with allowing it to replicate with no ID columns set on the subscriber. I only have 5, so if there was a problem I could just set the ID columns on the subscriber and then change my DB Connection in my WEB.CONFIG to point to it. This is however a temporary solution.

Thanks, I found your info most helpful.

error 8102:cannot update identity column

HI

I have 2 SQL Servers 2000 enterprise version. I setup a transactional replication between them. There is a table using identity feature and set to not for replication. On the server A as publication, B as Subscriber. I created table in B manually and set the name conflict in article default to keep existing data. When it replicates at the first time, everything is fine, but if there is some data added to the table in Server A, the replication fails, saying cannot update indentity column.

I read about something on add Set Identity on/off thing. But I dont know which SP I should add this to. Please help. As detail as possible. Thanks.There is a bug in the Managed Range Identity system. In Merge Replication I have found that while creating a publication using range identities it will work only the first time. Changing settings such as Publisher range or subscriber range does not have an effect. Also, deleting the subscriptions and publications DOES NOT remove the constraints from the affected subscriber tables. Also, the entry for the database in the replication distribution DB table MSrepl_identity_range does not get deleted and therefore the next time the publication is run it will not take the correct settings. In order to successfully recreate the subscription you must delete the row(s) from the afore mentioned table and remove all constraints manually. Only then will it work. If the constraints are not removed the subscriber will show the identity range as used up and will demand a re-sync, and the re-sync will have no effect.

Applies to SQL Server 2000 All SPs

You MUST delete all publications on the affected database and subscriptions first, then run SP_RemoveDBReplication DBName on the publisher and subscribers first then follow the steps above. You can sp_help to see what constraints are on the tables.|||I am sorry, if I undertsand you correctly what you are saying is that I cannot have a replication set up for identity enabled table?|||Originally posted by cobraeyez
I am sorry, if I undertsand you correctly what you are saying is that I cannot have a replication set up for identity enabled table?

no, you can set it up. Just keep in mind that there is a bug in SQL Server relating to the identitiy managing subsystem. What you want to do is delete the pub, subscriptions, drop the Check Constraints on all the subscription tables, find the Identity_Range table in the Replication Distribution database and delete all entries for the database that you are working on. Then recreate the publication and subcription and you will be fine.|||hey. thanks that SP_RemoveDBReplication thing works. :)|||ok, maybe I speak too quick, I think the insert works, but the update doesn't . If I modify a record in the publisher the replication failed again.

And what I did for fix the insert record is remove the publications and subscriptions and ran that RemoveReplication command on the DBs, there is no records in MSrepl_indentity_range table for me to delete.|||ok. The article containing the identitiy field is not set to range identitiy. When you set up the publication you need to click the "..." button to the right of the article name, then click the "Identity Range" tab. Enter your values and click OK to start the Identitiy Ranging.|||thanks, you mean the table article property button (...) right? In there, there is no Identity Range Tab. There are 3 tabs in it. General, Command, Snapshot.|||You will only have that tab on articles that have an identitiy column. Are you sure the article has an Identitiy?|||yes, and I am using sql 2000, identity is enabled and set not for replication|||You've got much of suggestions to the question, but I hope this MSDN Link (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01d4.asp) also gives you much insight to resolve.

HTH|||I had a similar problem and it was because I had triggers and DRI on the same table