Prevent updates, inserts and deletes from being replicated

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

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.