"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
>

"Eli Tucker" <eli-news@nerdmonkey.com> wrote in message
news:1105409204.644780.269050@c13g2000cwb.googlegroups.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?
>

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....
>
> rep.ReinitializeSubscription(True)
>
> -- Darren Shaffer
>
>
>
> "Eli Tucker" <eli-news@nerdmonkey.com> wrote in message
> news:1105409204.644780.269050@c13g2000cwb.googlegroups.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?
> >