"Do not replicate DELETE statements" still replicates deletes to subscribers

I previously asked What does "Do not replicate DELETE statements" do? to make sure I was correct on my thinking of what "Do not replicate DELETE statements" does. And after finding out it does what I would like it to do, create an archive db, I've tried several testing scenarios to see if it would work. So far I have been unable to not replicate delete statements. I'm not sure if I'm not setting a property right or what and any guidance would be appreciated.

Here is what I've done.

Created a blank db to be used as a subscriber and created a test db with some random data in a table.

Setup a New Publication on the db with the random data as "Transactional Publication"

Selected the following articles and properties in the publication

Tables

DELETE Delivery Format = Do not replicate DELETE statements

Views

Default Values

Stored Procedures

Default Values

User Functions

Default Values

Selected the default options for the rest of the New Publication Wizard steps and clicked finish.

Created a Pull Subscription on the new publication that I just created.

Let it initialize.

Then did a select count(*) query on the test table on the publication (18k+ rows) and subscriber (18k+ rows)

Then did a delete t-sql from the test table on the publication.

Then did a select count(*) query on the test table on the publication (0 rows) and subscriber (0 rows).

Now shouldn't it not delete the records on the subscription db?

Could you query sysarticles table in your publication database and look at del_cmd column?

|||

This is a bug in the article properties wizard (UI), it's populating column sysarticles.del_cmd with value "NULL" or "SQL" instead of "NONE". Value of "NULL" is same as value of "SQL", which is why your delete statements are still getting replicated.

The best fix is to do one the following:

1. set everything up via TSQL and set sp_addarticle parameter @.del_cmd = 'NONE'

2. set everything up via UI and then for each article, run sp_changearticle to set del_cmd = 'NONE'. This may reinit your subscriptions and cause you to regenerate the snapshot.

Thanks for raising this to our attention, I'll file a bug and see if we can get it into SP2.