Transactional replication to update subscriber only if a specific column value is updated

There was a request to update the subscriber only if a specific column value in the publisher database table gets updated. These were the only updates that need to get replicated. Its more like the subscriber is least bothered of any updates except for this specific column. In this post I would like to share how this was accomplished.

1) Expand the subscriber database–> Programmability –>Stored procedures –> Right click on the stored proc dbo.sp_MSdel_dbo–> script stored procedure as–> Alter to–> New query window

2) Your stored proc may look something like this:

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))--check if the primary key has been updated
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

3) In this scenario I would like to update only if [col7] gets updated in the publisher and replicate only these updates in the subscriber. For this I would remove all the columns in both the updates above and leave [col7] as shown below:

/****** 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))--check if the primary key has been updated
begin
update [dbo].[2006] set
[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
[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

4) Now execute the stored proc to reflect the changes.

5) Try to update a record in the publisher and you will see if the updated record has updated the specific column value (which is desired) then it gets replicated to the subscriber. Any other updates will not be replicated.

Hope this is useful.

Advertisements
This entry was posted in SQL Server DBA Stuff and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s