Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mrzcrn
Starting Member
15 Posts |
 Posted - 2004-11-01 : 06:20:14
|
Hi,I have 2 database, one to store 1 week of data (Database A) and one for historical purpose to store 4 months of same data (Database B).In order to do that we install the replication between A to B.In order to not propagate the delete done in database A by the scheduled job that purge data older then 1 week, we modifiy the stored procedure of replication connected to the DELETE operation. So the DELETE operation is not propagate to database B.Everything is working well but we ave a problem if we delete a row in the database B and the same row still exists and is UPDATED in database A, the replication of the UPDATE command got an error because it deosnt find the corresponding record in database B and replication process stop for this error.The only solution we can do is to start again the snapshot agent between A to B, but we lose all the old data in database B.I dont know if what i written above is clear...my english doesnt help .... Someone has a better solution to do this work?Thanks maurizio carnesecchi |
|
|
Knarf180
Starting Member
42 Posts |
Posted - 2004-11-01 : 12:38:25
|
| If Database B is your historical record then you really shouldnt be deleting rows from it. What do you want to do if a record from B was deleted? Delete the one in database A aswell?You could set a trigger on database B to check for deletes and do what you need to resolve the issue. |
 |
|
|
mrzcrn
Starting Member
15 Posts |
Posted - 2004-11-01 : 12:49:42
|
| Yes Knarf,i m agree with you, but u dont think is so bad that replication crash and there are no ways to start it again unless u start again the snapshot and lose all old data in database B?Another problem is that i cannot delete raws in database A (i can only delete rows if i m really sure this records will be not more present on it).This can happen when suppose a new records is create on database A and it is copied into database B by replication.After we delete that record in database A .... but the delete is not propagate because we want propagate the only insert and update.If later we need to insert again the previous delted record the result is that again the replication crash because this record is already present in database B.I know, we force the no-propagation of the delete but i found strange the replication will crash definitively.....Do you know if it is possible to trap this error in order to continue to run the replication.maurizio |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-11-01 : 21:49:38
|
| you could do backup before starting the snapshot. this way, you get the previous state of your database right before snapshot deletes all rows in the table.--------------------keeping it simple... |
|
|
|
|
|
|
Replication Transactional