469,167 Members | 1,150 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,167 developers. It's quick & easy.

Transaction Replication & Data archiving on SQL server 2000

Hi techies
I have set up a Transaction replication from My Primary Server to
Secondary Server on Orders table.

Thousand of records gets inserted on Orders every hour which get
replicated on the secondary server. it works fine

reporting apps uses Secondory server's Orders table data for generating
reports .

The Problem :
Let say if i want to Remove older records from Orders table in the
primary serverwith out reflecting this change on the secondary server.

is there a way to PREVENT this operation /transaction to be propogated
to the secondary server.
Note : i am moving the records to another table (orders_Archive ) and
deleteing the rows from orders table . Also I need all the rows to be
present on the secondary server table.

Please advice ASAP

Regards,
Raj

Jul 23 '05 #1
4 2043
In a case like this, why don't you stop the replication and do the
following:
1. create a linked server on the second server to the first server
2. create a stored procedure on the second server that gets data from the
1st server over to the second server.
this would be based on a query that only extracts orders from today's
date .
3. run the stored procedure as a job and run it every hour or every 2 hours
so that all records are moved to the 2nd server.

Then you can delete orders on the 1st server and it would not be deleted on
the 2nd server.

Oscar....

<ra********@yahoo.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...
Hi techies
I have set up a Transaction replication from My Primary Server to
Secondary Server on Orders table.

Thousand of records gets inserted on Orders every hour which get
replicated on the secondary server. it works fine

reporting apps uses Secondory server's Orders table data for generating
reports .

The Problem :
Let say if i want to Remove older records from Orders table in the
primary serverwith out reflecting this change on the secondary server.

is there a way to PREVENT this operation /transaction to be propogated
to the secondary server.
Note : i am moving the records to another table (orders_Archive ) and
deleteing the rows from orders table . Also I need all the rows to be
present on the secondary server table.

Please advice ASAP

Regards,
Raj

Jul 23 '05 #2
Raj
Well I need any changes to existing records ( column updates) also be
propogated to secondary automatically,without me writing any code to
detect the changes.... .

This is taken care in the transactional replication .

The issue of Archiving/delete a lot of record is causing a issue....

Jul 23 '05 #3

<ra********@yahoo.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...
Hi techies
is there a way to PREVENT this operation /transaction to be propogated
to the secondary server.
Note : i am moving the records to another table (orders_Archive ) and
deleteing the rows from orders table . Also I need all the rows to be
present on the secondary server table.

Hand Edit the update/delete replication stored procs on the subscribing
server.

Please advice ASAP

Regards,
Raj

Jul 23 '05 #4
Thanks Greg .. I will Edit the replicaiton SP

Raj

Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Craig HB | last post: by
2 posts views Thread by Martin McNally | last post: by
4 posts views Thread by yashgt | last post: by
3 posts views Thread by Gert van der Kooij | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.