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

  • Question

  • 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

Answers

  • Why dont you modify after the designer generated the code and then throw away the sdf file since you now have the code.
    At this point, modify your code and then deploy this as your code.
    Next time around, when it geenrates the sdf file, your filter clauses should take effect for the first sync itself.
    This posting is provided AS IS with no warranties, and confers no rights
    Tuesday, March 16, 2010 6:13 AM

All replies

  • Why dont you modify after the designer generated the code and then throw away the sdf file since you now have the code.
    At this point, modify your code and then deploy this as your code.
    Next time around, when it geenrates the sdf file, your filter clauses should take effect for the first sync itself.
    This posting is provided AS IS with no warranties, and confers no rights
    Tuesday, March 16, 2010 6:13 AM
  • Hi PHT,

    just to expound on Mahesh's suggestion.

    The designer generated code has codes as well to indicate table creation options (look for InitializeTableOptions in the designer generated code). So, if you have modified the designer generated code already to include your filter, you can delete the designer generated SDF, just replace it with a new one having the same location and filename as the one in your connection string. When you run the first sync,  the table creation option kicks in and will re-create the tables and populate it using your filter.
    Tuesday, March 16, 2010 9:47 AM
    Moderator