SQL Server Performance

Automatic Identity range support problem

Discussion in 'SQL Server 2005 Replication' started by L0st_Pr0phet, Jul 8, 2009.

  1. L0st_Pr0phet Member

    Hi,
    A Client of our is trying to replicate a article that is currently apart of a merge replication publication. They are trying to setup a non updating transact replication but get the following error:
    Automatic identity range support is useful only for publications that allow updating subscribers. changed database context..... (Microsoft SQL Server, Error: 21231)
    Any Ideas?
    cheers Stew
  2. satya Moderator

    When Subscriber inserts are replicated back to the Publisher, identity columns must be managed to avoid assignment of the same identity value at both the Subscriber and Publisher. Replication can manage identity ranges automatically or you can choose to manually handle identity range management. You can use replication stored procedures to specify identity range management options when an article is created.
    BOL specifies that:
    At the Publisher on the publication database, execute sp_helparticle and note the value of identityrangemanagementoption in the result set. If this value is 0, automatic identity range management is not enabled.
  3. L0st_Pr0phet Member

    Hi,
    Well I am guessing they do use this, and it will be set, so does that mean then you cant publish this table again as part of a transactional replication?
    stew
  4. satya Moderator

    You need to exclude this column out of replication, why to republish again.
  5. L0st_Pr0phet Member

    Hi,
    To be honest I am not too sure why they want to re-publish, we dont really support this customer, but I expect its maybe for a test server.
    I am going to contact them today and go through their requirements. Thanks for the reply merge replication isnt one of my strong points to say the least.
    stew
  6. L0st_Pr0phet Member

    Hi,
    The identity range is part of the primary key which means I cant exclude the column from the replication.
    I guess the only other way to replicate this to another server would be using mirroring?!?!?
    stew

Share This Page