Re: Removing rows on subscribers, but not on publishers

• Previous message: Darren Shaffer: "Re: Import from XML file"
• Next in thread: Darren Shaffer: "Re: Removing rows on subscribers, but not on publishers"
• Maybe reply: Darren Shaffer: "Re: Removing rows on subscribers, but not on publishers"
• Maybe reply: scott kramer via SQLMonster.com: "Re: Removing rows on subscribers, but not on publishers"
• Messages sorted by: [ date ] [ thread ]

Date: Sat, 8 Jan 2005 10:57:57 -0500

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.googlegroups.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
>

• Previous message: Darren Shaffer: "Re: Import from XML file"
• Next in thread: Darren Shaffer: "Re: Removing rows on subscribers, but not on publishers"
• Maybe reply: Darren Shaffer: "Re: Removing rows on subscribers, but not on publishers"
• Maybe reply: scott kramer via SQLMonster.com: "Re: Removing rows on subscribers, but not on publishers"
• Messages sorted by: [ date ] [ thread ]