none
DbSyncException during select changes stage. RRS feed

  • Question

  • The scenario is propagating a deleted record from local (SqlCe4.0) to remote (SqlServer 2005). (Sync Framework 2.1)

    I get an DbSyncException which indicates a problem at the Selecting Changes stage:
    Cannot enumerate changes at the RelationalSyncProvider for table 'Building'.  Check the inner exception for any store-specific errors.

    [inner exception stack trace]
      at System.Data.SqlServerCe.SqlCeDataReader.ProcessResults(Int32 hr)
      at System.Data.SqlServerCe.SqlCeDataReader.IsEndOfRowset(Int32 hr)
      at System.Data.SqlServerCe.SqlCeDataReader.Move(DIRECTION direction)
      at System.Data.SqlServerCe.SqlCeDataReader.Read()
      at Microsoft.Synchronization.Data.RelationalSyncProvider.EnumerateChangesInternal(DbSyncScopeMetadata scopeMetadata)

    The specfic table I'm having trouble with has both Ntext and Image datatypes (other simple tables work).

    I'm wondering if this is relevent as when I try to run the Enumerate changes query (from the trace log) on the local CE database I see the same(?) 'Not Implemented' error.  
    Both halves of the query work but on their own but the UNION ALL clause seems incompatible.

    Any ideas how to track down where I've gone wrong or comment if this is a known limitation?

    Thanks

    James



    • Edited by Yahmez Thursday, August 16, 2012 1:36 AM
    Thursday, August 16, 2012 1:36 AM

Answers

  • do you have any other details on the 'Not implemented' error? this seems to be an error on the SQL Ce side rather than Sync Fx.
    • Marked as answer by Yahmez Friday, August 17, 2012 4:11 AM
    Thursday, August 16, 2012 10:21 AM
    Moderator

All replies

  • do you have any other details on the 'Not implemented' error? this seems to be an error on the SQL Ce side rather than Sync Fx.
    • Marked as answer by Yahmez Friday, August 17, 2012 4:11 AM
    Thursday, August 16, 2012 10:21 AM
    Moderator
  • 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);            
            }     

        





    • Edited by Yahmez Friday, August 17, 2012 6:54 AM
    Friday, August 17, 2012 3:52 AM
  • I know this is old now, but I am having the same issue. 

    I am unable to get the ._adapters for the local sqlce, basically there are 0 adapters.

    Does anyone know hoe to configure the adapters for the SqlCe or another way of removing the "ORDER BY" clause?

    Thanks

    Ian

    Thursday, August 1, 2019 6:00 AM