SQL Server - Filter records during Transactional Replication.

Asked By Query Builder

12-Apr-07 02:09 PM

Hi All,

I have a table with a column DeletedDate which stores a logical delete
of a record.

I need to set up transactional replication for reporting purposes that
this deleted records should not be replicated to the subscriber. That
is, if i see a value on the DeletedDate, I don't want that record to
be picked up for replication.

At the same time, when someone marks the record for deletion (by
putting a date on the DeleteDate column), I want that record to be
deleted on the subscriber database. (I can also set up a job to do the
deletes on the subscriber but i'd rather let the replication take care
of it).

Can this scenario be implemented in Microsoft SQL 2000?  I would
appreciate any ideas / thoughts in this matter.

Thanks in advance,
Aravin Rajendra.

12-Apr-07 02:42 PM

I think you may miss the point of Replication.

Yes, you can set up a Vertical Filter to not Replicate the DeletedDate.

However, if there is a value in DeletedDate but you only want to delete
record on subscriber, that means they (Publisher and subscriber) are non in
sync. all at.
What if next time you re-initiate the Snapshot agent again?  When it pushes
from Publisher to Subscriber, you will still get the same schema and data as
Publisher.

You may want to consider dropping records on pubisher.

Ed

13-Apr-07 08:35 AM

Basically it looks like you are doing soft deletes.

In this case you want a filter on your table that looks like this where
deleteddate is null

Then you want to modify your modify replication proc so that if the update
for this table involves a value for the deleteddate column that it does not
update this row but rather deletes it.

But I'm a little confused - you say " if i see a value on the DeletedDate, I
don't want that record to be picked up for replication."

But then in the next statement you say "when someone marks the record for
deletion (by putting a date on the DeleteDate column), I want that record to
be
deleted on the subscriber database"

These sound like mutually exclusive statements.

--
Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

13-Apr-07 01:00 PM

I interpreted the statements as "if DeleteDate is already non-null then
don't replicate" and "if DeleteDate becomes non-null then delete from
subscriber".  In particular, a row with DeleteDate non-null might have
some other column changed (in which case it shouldn't be replicated), or
might have DeleteDate set back to null (in which case it should be
replicated).

13-Apr-07 06:24 PM

Why not replicate the table as per normal and have a view on the subscriber
to filter the records you want. If it is absolutely necessary to hve the same
tablenqme you could replicate to another tablename and have the view use the
original tablename.
Paul Ibison

16-Apr-07 12:41 AM

985427C98A3A@microsoft.com...

Another option is to replace the stored procs on the subscriber with your
own, which isn't hard to do.



--
Greg Moore
SQL Server DBA Consulting           Remote and Onsite available!
Email: sql  (at)  greenms.com          http://www.greenms.com/sqlserver.html

16-Apr-07 09:10 AM

True - this achieves the same end and is not difficult. Probably sounds picky
but I tend to shy away from changing the setup in this sort of way if there
is a standard alternative. If you only have a few servers then there's no
problem, but if you have 100s of SQL Servers then I'd recommend using the out
of the box solution.
Rgds,
Paul Ibison

20-Apr-07 10:55 AM

First, Thanks for you input.

I apologize for the confusion. Ed Murphy is correct.

Here is a clear explanation of the situation.

Lets say the table has 10 records.
2 of them have a DeleteDate value in the column.

So, I want the 8 records to be replicated to the subscriber.

If a user goes and updates another record with a value in the
deleteDate, I want that record to be deleted in the subscriber. (Now
out of 10, three of them are marked with a value in the DeleteDate).

Also, if a user goes and removes the DeleteDate value on one of the 3
records, I want that record to be inserted into the subscriber table.

I am going to try the way Hilary mentioned and let everyone know the
results.

Thanks again for the help....

Regards,
Aravin