Results 1 to 12 of 12
  1. #1
    Join Date
    May 2003
    Posts
    11

    Angry Unanswered: 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.

  2. #2
    Join Date
    May 2003
    Location
    California
    Posts
    7

    Talking Bug WorkAround

    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.

  3. #3
    Join Date
    May 2003
    Posts
    11
    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?

  4. #4
    Join Date
    May 2003
    Location
    California
    Posts
    7
    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.

  5. #5
    Join Date
    May 2003
    Posts
    11
    hey. thanks that SP_RemoveDBReplication thing works.

  6. #6
    Join Date
    May 2003
    Posts
    11
    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.

  7. #7
    Join Date
    May 2003
    Location
    California
    Posts
    7
    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.

  8. #8
    Join Date
    May 2003
    Posts
    11
    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.

  9. #9
    Join Date
    May 2003
    Location
    California
    Posts
    7
    You will only have that tab on articles that have an identitiy column. Are you sure the article has an Identitiy?

  10. #10
    Join Date
    May 2003
    Posts
    11
    yes, and I am using sql 2000, identity is enabled and set not for replication

  11. #11
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    You've got much of suggestions to the question, but I hope this MSDN Link also gives you much insight to resolve.

    HTH
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  12. #12
    Join Date
    Nov 2003
    Location
    Wellington NZ
    Posts
    4

    maybe you have triggers and dri

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •