sorry that went out prematurely
do make your filter unresponsive to deletes (and inserts, deletes) while
doing batch deletes do this
alter procedure [dbo].[FLTR_authors_1__58] for replication as
if exists (select * from [dbo].[authors] where 1=1) return 0 else return 0
When you have finished return your filter to normal like this
alter procedure [dbo].[FLTR_authors_1__58] for replication as
if exists (select * from [dbo].[authors] where 1=1) return 1 else return 0
If you do incorporate custom business logic into your delete proc you will
have to modify your delete proc to look like this:
create procedure "sp_MSdel_authors" @pkc1 varchar(11)
as
delete "authors"
where "au_id" = @pkc1
and datecolumn >getdate()-365
--if @@rowcount = 0
-- if @@microsoftversion>0x07320000
-- exec sp_MSreplraiserror 20598
GO
If you don't make an adjustment for a date range in your delete proc you
will get data consistency errors.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message
news:uQsj%23OppEHA.3572@TK2MSFTNGP10.phx.gbl...
> such business logic is best encapsulated in custom replication stored
> procedures. So you could have the delete procedure only delete rows if the
> date range is less than one year.
>
> This way legitimate deletes of data younger than a year will be replicated
> but deletes which are intended to delete old data on your OLTP server will
> not be replicated.
>
> Another hint is to put bogus filters on your articles ie where 1=1 and
> modify the filter procs when you are doing deletes on your OLTP database
so
> they will always return a 0
>
> so do an alter on your filter proc so it looks like this
>
> create procedure [dbo].[FLTR_authors_1__58] for replication as
> if exists (select * from [dbo].[authors] where 1=1) return 0 else return 0
>
> instead of
>
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
>
http://www.nwsu.com/0974973602.html >
>
> "csl" <kcl1998@hotmail.com> wrote in message
> news:%23ssE4ujpEHA.1688@TK2MSFTNGP10.phx.gbl...
> > We want to create a reporting database from our OLTP database. We are
> > thinking to take an initial snapshot and then use transactional
> replication.
> > However, we also want to keep the OLTP database with only one year of
> data.
> > If after one year, we remove records from OLTP, the transactional
> > replication will be replicated to the reporting database. That means we
> are
> > losing data in the reporting database. What is the best way to do this?
> >
> >
>
>
