Post reply

Help with identity coulmn in transactinal replication

  • December 5, 2007 at 5:40 pm

    #95519

    Hi All,

    I have a transactional replication and want to add one column with identity seed 1 and increment 1. Many forums says that if i want to enable automatic identity range handling then i should enable it at the time of creating the publication. is there any way i can do it now, that i have a running transactional replication.

    Any help with a bit of detail as to how to do it is really appreciated.

    Thanks

  • December 10, 2007 at 11:06 pm

    #758840

    drop subscriptions to article / drop article / make schema changes / add subscription

    exec sp_dropsubscription @publication = 'MyPubName'

    , @article = 'MyTable'

    , @subscriber = 'all'

    exec sp_droparticle @publication = 'MyPubName'

    , @article = 'MyTable'

    -- make change

    -- add table

    exec sp_addarticle @publication = N'MyPubName', @article = N'MyTable', @source_owner = N'dbo'

    , @source_object = N'MyTable', @destination_table = N'MyTable', @type = N'logbased', @creation_script = null

    , @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F3, @status = 16

    , @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_MyTable', @del_cmd = N'CALL sp_MSdel_MyTable', @upd_cmd = N'MCALL sp_MSupd_MyTable', @filter = null

    , @sync_object = null, @auto_identity_range = N'false'

    GO

    exec sp_addsubscription @publication = 'MyPubName'

    , @article = 'MyTable'

    , @subscriber = 'SvrName'

    , @destination_db = 'MyDB'

    , @sync_type = 'automatic'

    Enjoy,

    ChrisB MCDBA

    MSSQLConsulting.com

    Chris Becker bcsdata.net

  • December 11, 2007 at 3:27 am

    #758878

    Amit,

    If you want to simply add an identity column to your table which will be replicated then you can easily do it with sp_repladdcolumn, however, the subscriptions need reinitializing. Issue the following command in the publisher db:

    sp_repladdcolumn @source_object = 'MyTable',

    @column = 'NewColumn',

    @typetext = ' int identity(1,1)',

    @force_reinit_subscription = 1

    Please note that the identity value for the current records will be created in an undefined order.

    Erik

    -- Erik http://blog.rollback.hu

Viewing 3 posts - 1 through 2 (of 2 total)

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