locked
updating subscriber data based on condition RRS feed

  • Question

  • OS: W2K8 R2 SP1 SE

    SQL Server: SS2K8 R2 SP1 SE

    In transaction replication is there a way that I can restrict the updates specific only to a column?

    Lets say if updates occur on source table(publisher database) on column a, column b and column c then I dont want any thing to get updated in the destination database tables (subscriber database). But if update occurs only on column d on source table(publisher database) then I want it to get updated in destination table too (subscriber database). This is the condition. Is this possible?

    Thanks Again

    • Moved by Tom Phillips Wednesday, February 6, 2013 2:44 PM Replication question
    Tuesday, February 5, 2013 10:56 PM

Answers

  • leave the col6 and remove all the columns  in both the update statements

    update dbo.2006 set [col6] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else [col6] end where [Date] = @pkc1


    Hope it Helps!!


    • Edited by Stan210 Wednesday, February 6, 2013 4:05 PM
    • Marked as answer by AccDba111 Wednesday, February 6, 2013 5:03 PM
    Wednesday, February 6, 2013 3:55 PM

All replies

  • I assume updating column d means insert, update and delete on column d. You can achieve this by adding column filter while creating publication, and remember that the destination table will have just the columns which you have selected. Also, note that the column d must be primary key column or if there is another column which is primary key then it also should be selected. You cannot use tables for transactional replication which does not have primary key.

    Ex:

    Table - Test

    Columns - A,B, C,D and If A is primary key

    You can setup transactional replication selecting all the columns, or some of the columns, but the column A should be mandatorily selected.

    You can choose in the below screen on which columns you want to publish

    Wednesday, February 6, 2013 12:18 AM
  • I assume updating column d means insert, update and delete on column d. You can achieve this by adding column filter while creating publication, and remember that the destination table will have just the columns which you have selected. Also, note that the column d must be primary key column or if there is another column which is primary key then it also should be selected. You cannot use tables for transactional replication which does not have primary key.

    Ex:

    Table - Test

    Columns - A,B, C,D and If A is primary key

    You can setup transactional replication selecting all the columns, or some of the columns, but the column A should be mandatorily selected.

    You can choose in the below screen on which columns you want to publish

    Thanks for the reply. In the replication I am only inserting and updating and selecting do not do anything for the delete part.  I will be selecting all the columns in the publisher database's table. ColA the primary key, ColB, ColC and ColD.But is it possible to update the destination table(Subscriber DB) only if ColD has been updated which is not a primary key and no changes to destination table (Subscriber DB) if the other columns get updated in the source.

    Thanks

    Wednesday, February 6, 2013 3:02 AM
  • Thanks for the reply. In the replication I am only inserting and updating and selecting do not do anything for the delete part.  I will be selecting all the columns in the publisher database's table. ColA the primary key, ColB, ColC and ColD.But is it possible to update the destination table(Subscriber DB) only if ColD has been updated which is not a primary key and no changes to destination table (Subscriber DB) if the other columns get updated in the source.

    Thanks

    one way to not replicate the delete statements are, in the articles properties--> choose, "Do not Replicate Delete Statements" in the Delete Delivery Format.

    For second question,  in the article properties, for "Update Delivery Format"if you choose "call <<stored procedure>>".You will see stored procedures for update statements in your stored procedures folder in object explorer. It will similar to sp_MSupd_<<TableNAme>>. modify this procedure to include just the column you want to update. then, you can only update only the columns you want but not the rest. you can still insert into these other columns



    Hope it Helps!!





    • Edited by Stan210 Wednesday, February 6, 2013 4:12 AM
    Wednesday, February 6, 2013 3:54 AM
  • Thanks
    Wednesday, February 6, 2013 4:42 AM
  • I found the stored proc that needs to be modified. I am not sure where the changes have to be made and I need some help from experts on this. In the stored proc below I want to update subscriber tables only if col6 value gets updated and do nothing if other columns get updated.

    USE [CCF_SubscriberDB]
    GO
    
    /****** Object: StoredProcedure [dbo].[sp_MSupd_dbo2006] Script 
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER procedure [dbo].[sp_MSupd_dbo2006]
    @c1 datetime = NULL,
    @c2 int = NULL,
    @c3 int = NULL,
    @c4 int = NULL,
    @c5 int = NULL,
    @c6 int = NULL,
    @c7 money = NULL,
    @c8 nvarchar(20) = NULL,
    @pkc1 datetime = NULL,
    @bitmap binary(1)
    as
    begin 
    if (substring(@bitmap,1,1) & 1 = 1))--I think its checking if the primary key has been updated here
    begin 
    update [dbo].[2006] set
    [Date] = case substring(@bitmap,1,1) & 1 when 1 then @c1 else [Date] end,
    [col1] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [col1] end,
    [col2] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [col2] end,
    [col3] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [col3] end,
    [col4] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [col4] end,
    [col5] = case substring(@bitmap,1,1) & 32 when 32 then @c6 else [col5] end,
    [col6] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else [col6] end,
    [col7] = case substring(@bitmap,1,1) & 128 when 128 then @c8 else [col7] end
    where [Date] = @pkc1
    if @@rowcount = 0
    if @@microsoftversion>0x07320000
    exec sp_MSreplraiserror 20598
    end 
    else
    begin 
    update [dbo].[2006] set
    [col1] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [col1] end,
    [col2] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [col2] end,
    [col3] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [col3] end,
    [col4] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [col4] end,
    [col5] = case substring(@bitmap,1,1) & 32 when 32 then @c6 else [col5] end,
    [col6] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else [col6] end,
    [col7] = case substring(@bitmap,1,1) & 128 when 128 then @c8 else [col7] end
    where [Date] = @pkc1
    if @@rowcount = 0
    if @@microsoftversion>0x07320000
    exec sp_MSreplraiserror 20598
    end 
    end 
    
    GO
    

    Thanks a ton
    Wednesday, February 6, 2013 3:44 PM
  • leave the col6 and remove all the columns  in both the update statements

    update dbo.2006 set [col6] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else [col6] end where [Date] = @pkc1


    Hope it Helps!!


    • Edited by Stan210 Wednesday, February 6, 2013 4:05 PM
    • Marked as answer by AccDba111 Wednesday, February 6, 2013 5:03 PM
    Wednesday, February 6, 2013 3:55 PM
  • Thanks Stan. I tested it and its working fine.
    Wednesday, February 6, 2013 5:03 PM