Filter records during Transactional Replication. -- sql server replication

Query Builder
4/12/2007 11:09:06 AM

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.

Ed
4/12/2007 11:42:01 AM

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
[quoted text, click to view]

Hilary Cotter
4/13/2007 8:35:40 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

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server replication

sql server replication : Filter records during Transactional Replication.