Hi June - yes I think its a SQL CE limitation. (so I'll mark this as answered).
To anyone else who thinks they might have a a similar issue, I grabed the select changes query from the trace log.)
Its has this form - I found that it worked for me sql CE if I commented out the 'order by' clause. Otherwise I got the 'Not Implemented' error.
SELECT
--YourBaseTbl columns...blah blah,
0 sync_row_is_tombstone,
[base].[__sysChangeTxBsn] [sync_row_timestamp], [base].[__sysTrackingContext], COALESCE([base].[__sysInsertTxBsn], 1) [base_create_bsn],
[side].[sync_update_bsn] [side_update_bsn], [side].[sync_update_peer_key], [side].[sync_update_peer_timestamp], [side].[sync_create_peer_key], [side].[sync_create_peer_timestamp]
FROM [YourBaseTbl] [base]
LEFT JOIN [YourBaseTbl_tracking] [side]
ON ([base].[ID] = [side].[ID])
WHERE (([base].[__sysChangeTxBsn] > @sync_min_timestamp))
UNION All
SELECT
--null or default placeholders for YourBaseTbl columns...,
[side].[sync_row_is_tombstone], 0 sync_row_timestamp, cast(NULL as uniqueidentifier) __sysTrackingContext, 0 base_create_bsn, [side].[sync_update_bsn] [side_update_bsn], [side].[sync_update_peer_key], [side].[sync_update_peer_timestamp], [side].[sync_create_peer_key], [side].[sync_create_peer_timestamp]
FROM [YourBaseTbl_tracking] [side]
WHERE (([side].[sync_update_bsn] > @sync_min_timestamp)) AND ([side].[sync_row_is_tombstone] = 1)
--order clause that seams incompatible for the union all clause..
ORDER BY [base].[ID]
...I found this *very bad, no good* workarround which seems to rely on the union returning an the results in the correct order by default. This *looks* like it works but I'd have no confidence in it.
(it relies on Anoop Madhusudanan's Access Private Wrapper - http://www.amazedsaint.com/2010/05/accessprivatewrapper-c-40-dynamic.html)
Obviously open to suggestions that don't involve subverting encapsulation and relying on undocumented behaviour ;-)
static void localProvider_SelectingChanges(object sender, DbSelectingChangesEventArgs e)
{
dynamic apwProvider = new AccessPrivateWrapper(sender);
var adapterColection = (DbSyncAdapterCollection)apwProvider._adapters;
DbSyncAdapter srvcAdpt = adapterColection.Single(i => i.TableName == "YourProblemTable");
IDbCommand slctIncChgCmd = srvcAdpt.SelectIncrementalChangesCommand;
slctIncChgCmd.CommandText = slctIncChgCmd.CommandText.Replace("ORDER BY [base].[ID]", String.Empty);
}
