Delete records older than a certain period from the subscriber

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
I think you will have to run a job on the subscriber which will delete rows
which are older than a month.
The merge filter only filters modified/deleted/inserted rows. Not rows which
are not touched.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Janez" <janezcas@.yahoo.com> wrote in message
news:c697ea9c.0412080703.1135f892@.posting.google.c om...
> 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
|||Hi Hilary,
thanks for your response.
About your suggestion: I have thought about this too, but I believe that
this would delete the records also from the publisher at next sync which
is not what I want. I want only one month old records in the
subscriber's database, but publisher should have all records not just
one month old.
Another thing:
What do you mean by 'not touched'?
I always update records at the subscriber before each sync with the
dummy update of the date field to the same value, but the filter is not
evaluated.
Should this dummy update be enough to trigger filter evaluation?!
Regards
Janez
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||For that form of logic I normally use bi-directional transactional
replication.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Janez Cas" <janezcas@.yahoo.com> wrote in message
news:eUUJG8U3EHA.1264@.TK2MSFTNGP12.phx.gbl...
> Hi Hilary,
> thanks for your response.
> About your suggestion: I have thought about this too, but I believe that
> this would delete the records also from the publisher at next sync which
> is not what I want. I want only one month old records in the
> subscriber's database, but publisher should have all records not just
> one month old.
> Another thing:
> What do you mean by 'not touched'?
> I always update records at the subscriber before each sync with the
> dummy update of the date field to the same value, but the filter is not
> evaluated.
> Should this dummy update be enough to trigger filter evaluation?!
> Regards
> Janez
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!