Removing rows on subscribers, but not on publishers

Hello.
I'm looking for a way to remove data from a merge replicated database
that would be filtered out if it was synchronized for the first time
today. In addition, I don't want the new rows to be deleted from the
publisher's database.
Let me explain. We have several tables that look similar to the
following:
TableA:
Id uniqueidentifier
Value varchar(50)
TimeStamp datetime
We have a merge replication publication with filters that specify that
data with a TimeStamp older than 14 days should not be synchronized,
similar to the following:
exec sp_addmergefilter @.publication = N'MyPub', @.article = N'TableA',
@.filtername = N'TableA_TableB', @.join_articlename = N'TableB',
@.join_filterclause = N'{snipped}... and TableA.TimeStamp > ( GETDATE()
- 14 )', @.join_unique_key = 0
What ends up happening is that new rows with a recent TimeStamp is
synchronized to the client as desired. If the client syncs again after
14 days, however, this now "stale" data is still present on the client
-- it does not become deleted.
If we manually try to remove the stale data by running a query such as
"DELETE from TableA where TimeStamp > (GETDATE() - 30)", the data is
deleted. But the next time the database is synchronized, the data is
also deleted from the publishing database. This is not acceptable in
our application.
Some of our clients use SQL CE on Pocket PC's with limitted storage
capacity and thus will not be able to continue to store this old data.
Any ideas on how to delete the old data from the SQL CE subscriber's
databases without having the side effect of removing the data from the
publishing database?
Thanks in advance for your time.
- Eli Tucker
I think what you want to do is to manipulate your agents so that they change
to download only (-ExchangeType download) when you delete rows on your
subscriber, and then both (-ExchangeType both) under normal operations.
This will only work if the deletes occur in isolation. For instance if the
only activity occuring on your subscriber was the delete, this would work.
If there were deletes and updates and inserts occuring on your subcriber
with the ExchangeType of download the inserts and deletes would not make it.
"Eli Tucker" <eli-news@.nerdmonkey.com> wrote in message
news:1105046696.732620.46610@.f14g2000cwb.googlegro ups.com...
> Hello.
> I'm looking for a way to remove data from a merge replicated database
> that would be filtered out if it was synchronized for the first time
> today. In addition, I don't want the new rows to be deleted from the
> publisher's database.
> Let me explain. We have several tables that look similar to the
> following:
> TableA:
> Id uniqueidentifier
> Value varchar(50)
> TimeStamp datetime
> We have a merge replication publication with filters that specify that
> data with a TimeStamp older than 14 days should not be synchronized,
> similar to the following:
> exec sp_addmergefilter @.publication = N'MyPub', @.article = N'TableA',
> @.filtername = N'TableA_TableB', @.join_articlename = N'TableB',
> @.join_filterclause = N'{snipped}... and TableA.TimeStamp > ( GETDATE()
> - 14 )', @.join_unique_key = 0
> What ends up happening is that new rows with a recent TimeStamp is
> synchronized to the client as desired. If the client syncs again after
> 14 days, however, this now "stale" data is still present on the client
> -- it does not become deleted.
> If we manually try to remove the stale data by running a query such as
> "DELETE from TableA where TimeStamp > (GETDATE() - 30)", the data is
> deleted. But the next time the database is synchronized, the data is
> also deleted from the publishing database. This is not acceptable in
> our application.
> Some of our clients use SQL CE on Pocket PC's with limitted storage
> capacity and thus will not be able to continue to store this old data.
>
> Any ideas on how to delete the old data from the SQL CE subscriber's
> databases without having the side effect of removing the data from the
> publishing database?
> Thanks in advance for your time.
> - Eli Tucker
>
|||Hilary, thanks for the info. I'll keep this tip in mind, but it isn't
ideal for us since the client application both adds and updates new
rows between syncs.
Does anyone have any other ideas of how to do this?
|||Eli,
Instead of synchronizing from your subscriber code, if you *reinitialize*
the subscription
with the option to upload your latest changes before refreshing the
subscription tables,
your subscriptions will always contain only the 14 days of data your
publication filter dictates.
Take a look at the boolean value that ReinitializeSubscription takes...
rep.ReinitializeSubscription(True)
-- Darren Shaffer
"Eli Tucker" <eli-news@.nerdmonkey.com> wrote in message
news:1105409204.644780.269050@.c13g2000cwb.googlegr oups.com...
> Hilary, thanks for the info. I'll keep this tip in mind, but it isn't
> ideal for us since the client application both adds and updates new
> rows between syncs.
> Does anyone have any other ideas of how to do this?
>
|||Thanks, Darren. I haven't looked at the ReinitializeSubscription
option before -- it very well could work for us.
Is it basically equivalent to the following?
1. Synchronizing upload only
2. Deleting the SDF file
3. Performing an initial sync
Or is less data passed when using the ReinitializeSubscription(true)
method?
Thanks again!
- Eli
Darren Shaffer wrote:
> Eli,
> Instead of synchronizing from your subscriber code, if you
*reinitialize*
> the subscription
> with the option to upload your latest changes before refreshing the
> subscription tables,
> your subscriptions will always contain only the 14 days of data your
> publication filter dictates.
> Take a look at the boolean value that ReinitializeSubscription
takes...[vbcol=seagreen]
> rep.ReinitializeSubscription(True)
> -- Darren Shaffer
>
> "Eli Tucker" <eli-news@.nerdmonkey.com> wrote in message
> news:1105409204.644780.269050@.c13g2000cwb.googlegr oups.com...
isn't[vbcol=seagreen]