Prevent updates, inserts and deletes from being replicated
Business requirements may require that inserts, updates and deletes are not replicated. Find out how to prevent replication in this tip, one of 15 tips in 15 minutes.
The following is one of 15 replication tips in 15 minutes. Click here to return to the full list of tips.
15 of 15 replication tips: Prevent updates, inserts and deletes from being replicated
Sometimes your business requirements require that inserts, updates and deletes not be replicated. To do this, right click on your Publication, select Properties, click on the Articles tab, and click on the Browse button to the right of each table you wish to modify. Click on the Commands tab. For the Insert command (or Update or Delete as required), replace the stored procedure name with the work NONE.
Occasionally you will need to do deletes on a daily basis, but do a batch delete on a monthly basis. You do not want the batch delete to be replicated, but you do want the day-to-day deletes to flow. There are several approaches to this:
Sample script 2 illustrates this:
create database filter
go
create database filtersub
go
use filter
go
create table filter
(pk int not null identity constraint pk primary key,
charcol char(20),
date_time datetime default getdate())
go
insert into filter (charcol) values('test')
go
insert into filter (charcol) values('test')
go
insert into filter (charcol) values('test')
go
insert into filter (charcol) values('test')
go
sp_dboption 'filter','published','true'
go
sp_addpublication 'filter',@status='active'
go
sp_addpublication_snapshot 'filter'
go
create procedure [dbo].[FLTR] for replication as if exists (select * from
[dbo].[filter] where 1=1) return 1 else return 0
go
sp_addarticle 'filter','filter','filter',@destination_table='filter_sub',
@type='logbased manualfilter',
@filter='FLTR',@filter_Clause='1=1'
go
--we are making a subscription in the local database
sp_addsubscription 'filter','filter',@@servername
go
insert into filter (charcol) values('before change')
go
waitfor delay '00:01:00'
go
select * from filter_sub
go
--observer that the row has travelled
--now we will modify the proc to alway return false
alter procedure [dbo].[FLTR]
for replication as if exists (select * from
[dbo].[filter] where 1=1) return 0 else return 0
go
insert into filter (charcol) values('after change')
go
waitfor delay '00:01:00'
go
select * from filter_sub
go
--observe that the row has not arrived
--now let's delete everything
delete from filter
go
waitfor delay '00:01:00'
go
select * from filter
go
select * from filter_sub
go
--notice that there are no rows in filter, but all the rows are in filter_sub
Click here to return to 15 replication tips in 15 minutes.
About the author: Hilary Cotter has been involved in IT for more than 20 years as a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor of applied science degree in mechanical engineering from the University of Toronto and subsequently studied both economics at the University of Calgary and computer science at UC Berkeley. He is the author of a book on SQL Server transactional replication and is currently working on books on merge replication and Microsoft search technologies.
