Filter records during Transactional Replication.

[Query Builder]

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.

[Hilary Cotter]

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

"Query Builder" <qu**********@g mail.comwrote in message
news:11******** **************@ e65g2000hsc.goo glegroups.com.. .

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 Murphy]

Hilary Cotter wrote:

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.

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).

[Query Builder]

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

On Apr 13, 1:00 pm, Ed Murphy <emurph...@soca l.rr.comwrote:

Hilary Cotter wrote:

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.

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).