Replication Error on identity Column : Transactional Replication


    I have manually deleted data from a subscriber replicated table, and broken replication.

    So now data is building up in the publisher table, and not getting copied across.

    The table has an identity column, and the error is "Cannot update identity column 'Id' "

    I don't have permissions to view replication - the error was sent to me as a screenshot of replication monitor

    The DBA expects me to fix this. So I assume its within my permission set, I can modify both publisher and subscriber tables, but I can't even open the replication menu items in SSMS - (error cannot access distribution database)

    I think I have to synchronise the keys between the two tables.

    If  manually copy the missing rows from publisher to subscriber, with IDENTTIY_INSERT to ensure matching keys will this fix the issue?



    Can anyone suggest a way


  • I wish I had a solution for you... but I just can't help but ask "Why you would have wanted to delete rows in the replica?"  Given that just deleting those rows in the published table would achieve that goal, and deleting anything in the replica would break replication, what was the actual objective?   If you wanted the replica to not actually be a replica (and thus be different from the original), then replication was never the solution in the first place, as that is NOT achievable via replication.   Replication makes sure your data is a 100% duplicate of the original, and you can't make it different in ANY way without breaking replication, so if you need the two tables to actually be different in ANY way, then replication is NOT the solution.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • You might be able to resolve the problem, but need to make absolutely sure that you re-insert only the rows that you deleted, and not any new rows, or you could exacerbate the problem by causing duplicate key errors.

    The error is a bit confusing given what you described. "Cannot update identity column 'Id'" seems like an error you might get if you manually inserted or updated rows in the subscriber rather than deleted.  Did somebody reseed the identity property?

  • As to why .. I deleted some rows from the publisher first - then noticed the delete did not happen in the subscriber - so then I deleted from the subscriber  (thinking maybe I had broken something, and attempting a correction)  - maybe it was already broken - I'm not sure.

    The objective IS to have a read-only exact copy of the published table

    Subsequent to that rows added to the subscriber did not get replicated

    I noticed the keys are out of sync between the two tables SELECT IDENT_CURRENT - the subscriber is a few behind - so it is these I was thinking of manually adding

    There must be some other options for fixing replication

    Or will it have to be a backup restore on both pub and sub



  • Actually, you'll need the DBA to recreate the replication.   Steps are:

    1.) Stop the existing replication.

    2.) TRUNCATE the destination table.

    3.) DBA sets up replication again between the two.

    Once you are out of sync, then the responsibility belongs to the DBA to resolve, and NOT to a developer.   If your company expects otherwise, then they are engaging in intentional stupidity...   You will not be able to solve this problem just by adjusting data in the destination table.  Once replication either "gets behind" or is not working, the developers are no longer a part of the solution.


    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Removing the table article from the distribution and then re-adding would allow it to re-synch (assuming it drops/deletes/truncates & recreates).  You said you don't even have permission to view replication, so that would be up to the DBA.

    Is this production or another environment? It sounds like the DBA may be trying to teach a lesson and reinforce accountability. But if this is production, for the sake of the business, the business and proper functioning of the database needs to take priority. In which case, the DBA probably needs to take charge and resolve the problem, and then focus on training and accountability later. Everyone needs to understand what they can/should do to data, particularly when replication is involved.

    I've resorted to triggers on the subscriber side that raise an error to prevent users from making breaking changes.

  • Yes probably the DBA's subtle way of getting me to learn about replication, or to leave it well alone.

    And at least it was only the dev environment.

    Thanks for your help guys, much appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply