How do I truncate a table created by the Designer so I can perform a BiDirectional Sync to insert records meeting my WHERE criteria?

• Question

• 

  

  0

  Sign in to vote

  using C#, Sync Framework, Windows Mobile device, SQLCE, SQL Server 2008 with Change Tracking
  
  By design the Sync Designer downloads all data to the SDF file when the Designer finishes its work - I don't want all the data, but I realize I can't keep the designer from doing its thing.  I changed the IncrementalInserts and IncrementalUpdates SQL to include a WHERE clause ... works fine for subsequent syncs ... but does not remove records already on device that don't meet WHERE criteria - (I assume I can't just run a SQL command to delete the unwanted records from the client as I assume they will then also be deleted from server at the next sync).
  
  Question: how do I establish a starting point for the SDF on the local device?
  
  I have tried several options and the one that looks like it got me the furthest is to:
  
  1) deploy my solution to the device (which sends the sdf with unwanted records - records not meeting the WHERE criteria) - before deploying to "real" users I'd like to eliminate this by truncating MyTable before deploying it
  
  2) on the client run a SQL command to "DELETE FROM MyTable"
  
  3) on the client execute code to reset the anchor for MyTable:
            ClientSyncProvider sqlCeProvider = (ClientSyncProvider)syncAgent.LocalProvider;
            sqlCeProvider.SetTableReceivedAnchor("MyTable", new SyncAnchor());
  
  4) Synchronize (want it to be BiDirectional)
  
  
  #4 is where I am having problems.  If I perform a BiDirectional sync I get an error - I assumed because I changed the Anchor that BiDirectional needs ... don't know.  I can perform a Snapshot sync without errors and I get the intended records; however, when I then try to perform a bidirectional sync I get an error.
  
  Am I on the right track and if so, what am I missing?
  
  Thanks!
  
  ERROR:
  System.Reflection.TargetInvocationException was unhandled
    Message="TargetInvocationException"
    StackTrace:
         at System.Reflection.RuntimeMethodInfo.InternalInvoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean verifyAccess, StackCrawlMark& stackMark)
         at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
         at System.Reflection.MethodBase.Invoke(Object obj, Object[] parameters)
         at Microsoft.Synchronization.Data.ServerSyncProviderProxy.ApplyChanges(SyncGroupMetadata groupMetadata, DataSet dataSet, SyncSession syncSession)
         at Microsoft.Synchronization.SyncAgent.UploadChanges(SyncGroupMetadata groupMetadata)
         at Microsoft.Synchronization.SyncAgent.Synchronize()
         at PocketConnect.TestForm.menuSyncNow_Click(Object sender, EventArgs e)
         at System.Windows.Forms.MenuItem.OnClick(EventArgs e)
         at System.Windows.Forms.Menu.ProcessMnuProc(Control ctlThis, WM wm, Int32 wParam, Int32 lParam)
         at System.Windows.Forms.Form.WnProc(WM wm, Int32 wParam, Int32 lParam)
         at System.Windows.Forms.Control._InternalWnProc(WM wm, Int32 wParam, Int32 lParam)
         at Microsoft.AGL.Forms.EVL.EnterMainLoop(IntPtr hwnMain)
         at System.Windows.Forms.Application.Run(Form fm)
         at PocketConnect.Program.Main()
    InnerException: System.Net.WebException
         Message="WebException"
         StackTrace:
              at System.Web.Services.Protocols.SoapHttpClientProtocol.doInvoke(String methodName, Object[] parameters, WebClientAsyncResult asyncResult)
              at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
              at PocketConnect.WCFServiceLibraryWebReference.ClientDataCacheSyncService.ApplyChanges(SyncGroupMetadata groupMetadata, DataSet dataSet, SyncSession syncSession)
              at System.Reflection.RuntimeMethodInfo.InternalInvoke(RuntimeMethodInfo rtmi, Object obj, BindingFlags invokeAttr, Binder binder, Object parameters, CultureInfo culture, Boolean isBinderDefault, Assembly caller, Boolean verifyAccess, StackCrawlMark& stackMark)
              at System.Reflection.RuntimeMethodInfo.InternalInvoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean verifyAccess, StackCrawlMark& stackMark)
              at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
              at System.Reflection.MethodBase.Invoke(Object obj, Object[] parameters)
              at Microsoft.Synchronization.Data.ServerSyncProviderProxy.ApplyChanges(SyncGroupMetadata groupMetadata, DataSet dataSet, SyncSession syncSession)
              at Microsoft.Synchronization.SyncAgent.UploadChanges(SyncGroupMetadata groupMetadata)
              at Microsoft.Synchronization.SyncAgent.Synchronize()
              at PocketConnect.TestForm.menuSyncNow_Click(Object sender, EventArgs e)
              at System.Windows.Forms.MenuItem.OnClick(EventArgs e)
              at System.Windows.Forms.Menu.ProcessMnuProc(Control ctlThis, WM wm, Int32 wParam, Int32 lParam)
              at System.Windows.Forms.Form.WnProc(WM wm, Int32 wParam, Int32 lParam)
              at System.Windows.Forms.Control._InternalWnProc(WM wm, Int32 wParam, Int32 lParam)
              at Microsoft.AGL.Forms.EVL.EnterMainLoop(IntPtr hwnMain)
              at System.Windows.Forms.Application.Run(Form fm)
              at PocketConnect.Program.Main()
   

  • Moved by ErikEJMVP Monday, February 22, 2010 12:29 PM Better forum (From:SQL Server Compact)
  • Moved by Mahesh DudgikarMicrosoft employee Saturday, March 13, 2010 12:13 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])

  Tuesday, February 16, 2010 5:44 PM