July 5, 2006 at 12:56 pm
Hi,
If I want to replication from a publisher to a subscriber and periodically delete records from the publisher but don't want the data to be deleted subscriber, what type of replication can I use? Basically what I want to do is have the subscriber as a respositor/warehouse of data.
Thanks.
July 6, 2006 at 12:36 am
You can use Transactional replication, when you create transactional replication the subscriber database has three stored proc's for each table that is part of the replication. The stored proc's are called sp_MSins_<table name>(For Inserts), spMSupd_<table name>(For Updates) & spMSdel_<table name>(For Deletes). If you open the spMSdel_ and change the delete statements into "select 1".
The system will execute the stored proc but it will not run the remove any data from your subscriber.
Craig
July 7, 2006 at 7:31 am
Terence
Before you implement this, search for other discussions on this topic. There is a gotcha with this type of approach. If you will be updating this table at the publisher as well as deleting from it, you should be aware that certain types of updates (I think it's where you change the value of the primary key in a row) are actually executed as a delete followed by an insert. If you are "blocking" deletes then you may run into problems for that reason.
John
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply