Delete records older than a certain period from the subscriber

From: Janez (janezcas_at_yahoo.com)
Date: 12/08/04 

Date: 8 Dec 2004 07:03:23 -0800

Hi,

I am struggling with this for some time now:
I want all records older than a certain period (eg. one month) to be
deleted from the subscriber's database.
I have created sample database with only one table and one datetime
column, just for testing this. The table is filtered:
SELECT <published_columns> FROM [dbo].[table] WHERE DateField >=
DateAdd(month,-1,GetDate()) - so subscriber should only have records
entered last month.

So, if subscriber enters one record in the subscription db with
today's date and synchronizes immediatelly, record will remain in the
subscriber's database which is OK, but I want this record to be
removed from the subscriber's database when user will synchronize
someday in the future and this record will be older than a month.
However, this does not happen. I know that record won't be sent to the
publisher as a part of merge replication, if it was not changed
between synchronizations. For that reason, an update to the same value
is always performed on the subscriber's table before the
synchronization, eg. update table set datefield = datefield.
I can see in the merge agent history that this update is sent to the
publisher, but record still remains in the subscriber's db. It seems
to me that filter is not evaluated correctly or not evaluated at all.
If I specify reinitialization on the subscription, the record is
removed from the subscribers database, but I do not want to
reinitialize at each sync.

I have read numerous posts and noticed that this scenario should
work?!
Any idea what might be wrong?
I am using SQL 2000 with SP3.

Janez

Relevant Pages

  • Re: Delete records older than a certain period from the subscriber
    ... I think you will have to run a job on the subscriber which will delete rows ... The merge filter only filters modified/deleted/inserted rows. ... > deleted from the subscriber's database. ... > today's date and synchronizes immediatelly, ...
    (microsoft.public.sqlserver.replication)
  • Re: SQL2000 - Merge Replication - Timeout error
    ... The timeout is in your publisher and distributor server, ... Then, update the database statistics. ... Run profiler on the subscriber to see what is going on? ...
    (microsoft.public.sqlserver.replication)
  • Re: How to work with m:n relationships?
    ... Do NOT use SSN in a DBMS if you can possibly help it, ... you'll explain why you don't permit a subscriber to ... St and I will call it St-solution. ... We can use these objects to connect to a database, ...
    (comp.databases.theory)
  • Help with Transactional Replicatoin Setup
    ... After working on setting up replication for a while I have finally got ... I’ve got a development database with 3 tables using DRI and an identity ... our production server that has been in use for years now. ... the distributor/publication and the production server is the subscriber. ...
    (microsoft.public.sqlserver.replication)
  • How to work with m:n relationships?
    ... St and I will call it St-solution. ... Let Subscriber and SubsriberSt now get values: ... We can use these objects to connect to a database, ... then we can disconnect from the database. ...
    (comp.databases.theory)
Quantcast