Post reply

How to avoid to replicate some columns

  • April 2, 2007 at 6:57 am

    #171894

    I was just trying to replicate a table from a database to another. The 2 databases have the same schema, so every column of the 2 tables have the same types. My problem is just I don't want to replicate all the columns. If, for example, my tables have the columns A,B and C, I would like to replicate just the columns A and C and leave the value of B unchanged in the destination table if I modified the record or set a default value for it if I'm inserting a new record. I tried several times but SQL Server, during the replication process, drop the column B of the destination table...but I don't want it to drop it!

    The replication type I'm using is the transactional one.

    Thank you in advance for your help

    Luciano

  • April 3, 2007 at 5:49 pm

    #698429

    You can do a couple of things.

    1) Edit your replication stored procedures on subscribers. Replicated transactions call insert/update/del sp's on your subscriber. Open the insert & update and comment the column you don't want replicated.

    2) Using the replication wizard, you can specify which column(s) you want replicated.

    3) Use a script to add your article specifying the columns to be replicated:

    -- Adding article to publication - key is @vertical_partition = true

    exec sp_addarticle @publication = N'MyPub', @article = N'MyTable', @source_owner = N'dbo', @source_object = N'MyTable', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'MyTable', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'true', @ins_cmd = N'CALL [sp_MSins_dboMyTable]', @del_cmd = N'CALL [sp_MSdel_dboMyTable]', @upd_cmd = N'SCALL [sp_MSupd_dboMyTable]'

    -- Adding the article's partition column(s)

    exec

    sp_articlecolumn @publication = N'MyPub', @article = N'MyTable', @column = N'ColumnA', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1

    exec

    sp_articlecolumn @publication = N'MyPub', @article = N'MyTable', @column = N'ColumnC', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1

    Regards,

    ChrisB MCDBA MCSE OCP

    http://www.MSSQLConsulting.com

     

     

    Chris Becker bcsdata.net

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

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