none
事务复制,把自增id复制到订阅库 RRS feed

  • 问题

  • 自增ID涉及到业务逻辑,需要原样复制订阅库,但是复制的过程当中报错

     尝试的命令:
    if @@trancount > 0 rollback tran
    (事务序列号: 0x000055EB000000B0000500000000,命令 ID: 1)

    错误消息:
    当 IDENTITY_INSERT 设置为 OFF 时,不能为表 'CenterImportantPersons' 中的标识列插入显式值。 (源: MSSQLServer,错误号: 544)
    获取帮助: http://help/544
    当 IDENTITY_INSERT 设置为 OFF 时,不能为表 'CenterImportantPersons' 中的标识列插入显式值。 (源: MSSQLServer,错误号: 544)

    导致insert插入的数据不能传递到订阅库,请问是不是在创建发布的时候勾选一些选项

    还有一个问题:如果能将自增ID复制到订阅库,如果命令传递到订阅库执行的时候失败,导致事务回滚会不会影响订阅库造成自增ID自动增加了,但是数据没有插入到订阅库成功

    就像我们平时只在单个数据库里执行一条插入命令,然后用rollback tran来回滚事务,虽然数据没有插入到表,但是导致了

    自增ID增加了1

    2014年4月3日 15:35

答案

  • rmio大侠说的很对,需要设置'not for replication'

    When a replication agent connects to a table with any login, all of the NOT FOR REPLICATION options on the table are activated. When the option is set, SQL Server 2000 maintains the original identity values on rows added by the replication agent but continues to increment the identity value on rows added by other users. When a user adds a new row to the table, the identity value is incremented in the normal way. When a replication agent replicates that row to a Subscriber, the identity value is not changed when the row is inserted in the Subscriber table.

    http://technet.microsoft.com/en-us/library/aa237102(v=sql.80).aspx


    Please Mark As Answer if it is helpful.

    2014年4月4日 1:39
  • For bi-directional replication, you have to set different range for identity column (like one side uses odd numbers only and the other side uses even numbers only, or one side uses positive numbers and the other side uses negative numbers, or one side uses numbers smaller than certain value and the other side uses numbers larger than that value, ... all depends on business logic).
    2014年4月4日 3:49

全部回复

  • Did you specify 'not for replication' for identity column when create table?
    2014年4月3日 16:46
  • rmio大侠说的很对,需要设置'not for replication'

    When a replication agent connects to a table with any login, all of the NOT FOR REPLICATION options on the table are activated. When the option is set, SQL Server 2000 maintains the original identity values on rows added by the replication agent but continues to increment the identity value on rows added by other users. When a user adds a new row to the table, the identity value is incremented in the normal way. When a replication agent replicates that row to a Subscriber, the identity value is not changed when the row is inserted in the Subscriber table.

    http://technet.microsoft.com/en-us/library/aa237102(v=sql.80).aspx


    Please Mark As Answer if it is helpful.

    2014年4月4日 1:39
  • 有一个问题,加了'not for replication'之后是否禁用了订阅表的自增属性

    如果我切断复制之后,那么新插入的数据自增列sqlserver是否还会自动自增

    2014年4月4日 2:27
  • rmio大侠说的很对,需要设置'not for replication'

    When a replication agent connects to a table with any login, all of the NOT FOR REPLICATION options on the table are activated. When the option is set, SQL Server 2000 maintains the original identity values on rows added by the replication agent but continues to increment the identity value on rows added by other users. When a user adds a new row to the table, the identity value is incremented in the normal way. When a replication agent replicates that row to a Subscriber, the identity value is not changed when the row is inserted in the Subscriber table.

    http://technet.microsoft.com/en-us/library/aa237102(v=sql.80).aspx


    Please Mark As Answer if it is helpful.

    问一下刘大师,如果订阅表可更新,不是只读的, 我觉得应该会冲突吧,比如发布表有一条记录的自增id是5,但是这条记录还没有复制过去

    当前订阅表的自增id是4

    当我插入一条数据到订阅表的时候,这条记录的自增id是5,那么发布表的这条记录是不是不能复制过去?

    2014年4月4日 3:02
  • Yes, still works but may need reseed it first.
    2014年4月4日 3:02
  • 明白,幸亏订阅库是只读的,谢谢rmiao大侠
    2014年4月4日 3:07
  • For bi-directional replication, you have to set different range for identity column (like one side uses odd numbers only and the other side uses even numbers only, or one side uses positive numbers and the other side uses negative numbers, or one side uses numbers smaller than certain value and the other side uses numbers larger than that value, ... all depends on business logic).
    2014年4月4日 3:49
  • 谢谢,rmiao大侠,无论OLAP系统还是OLTP系统都一样,要考虑业务逻辑
    2014年4月4日 4:14