updating subscriber data based on condition

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

Answer 1

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

Answer 2

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

Answer 3

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!!

Answer 4

Thanks

Answer 5

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

Answer 6

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!!

Answer 7

Thanks Stan. I tested it and its working fine.