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.

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" <querybuilder@gmail.com> wrote in message
news:1176401346.580157.314620@e65g2000hsc.googlegr oups.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.
>

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

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...@socal.rr.com> wrote:
> 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).