locked
Synchronization help RRS feed

  • Question

  • i am doing a sync between a windows mobile databse table and a sql server 2008 DB table. Its a bi-directional sync. Now, what i want is during initial sync up i want to tell the sync service to download data based on a cloumn value. For exanple download all the records whose "City" column value is "Bangalore". I am using a web service for synchronization.

    How to use the SelectingChanges() event

    TIA
    Jagadeesh
    • Moved by Hengzhe Li Friday, April 22, 2011 2:27 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Thursday, July 23, 2009 7:51 AM

Answers

  • You can filter the data in the SelectIncrementalInsertsCommand on the SQL Server 2008 DB and nothing need to do at mobile db side. ex.

    CREATE PROCEDURE [dbo].[usp_Customers_SelectIncrementalInserts]
      @city nvarchar(128),
     @sync_initialized INT, @sync_last_received_anchor BIGINT, @sync_client_id_binary BINARY (16),
     @sync_new_received_anchor BIGINT, @sync_table_name NVARCHAR (128)
    AS
    BEGIN
     IF @sync_client_id_binary IS NULL -- for getting schame
      SELECT TOP 1 * FROM dbo.[Customers]
     ELSE
     IF @sync_initialized = 0 -- initial sync
      SELECT f.*
      FROM dbo.Bays f
      LEFT JOIN CHANGETABLE(CHANGES dbo.Customers, @sync_last_received_anchor) CT ON CT.[CustomerId] = f.[CustomerId]
      WHERE f.City = @city AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)
     ELSE
     BEGIN
      SELECT f.*
      FROM dbo.Customers f
      INNER JOIN CHANGETABLE(CHANGES dbo.Customers, @sync_last_received_anchor) CT ON CT.[CustomerId] = f.[CustomerId]
      WHERE f.City = @city AND (CT.SYS_CHANGE_OPERATION = ''I'' AND CT.SYS_CHANGE_CREATION_VERSION <= @sync_new_received_anchor
       AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary));
       
      IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) > @sync_last_received_anchor
       RAISERROR (N''SQL Server Change Tracking has cleaned up tracking information for table ''''%s''''. To recover from this error, the client must reinitialize its local database and try again'', 16, 3, @sync_table_name);
     END
    END


    For more details please read the section "How to: Filter Rows and Columns" in the SDK documentation.
    Thursday, July 23, 2009 10:51 PM

All replies

  • You can filter the data in the SelectIncrementalInsertsCommand on the SQL Server 2008 DB and nothing need to do at mobile db side. ex.

    CREATE PROCEDURE [dbo].[usp_Customers_SelectIncrementalInserts]
      @city nvarchar(128),
     @sync_initialized INT, @sync_last_received_anchor BIGINT, @sync_client_id_binary BINARY (16),
     @sync_new_received_anchor BIGINT, @sync_table_name NVARCHAR (128)
    AS
    BEGIN
     IF @sync_client_id_binary IS NULL -- for getting schame
      SELECT TOP 1 * FROM dbo.[Customers]
     ELSE
     IF @sync_initialized = 0 -- initial sync
      SELECT f.*
      FROM dbo.Bays f
      LEFT JOIN CHANGETABLE(CHANGES dbo.Customers, @sync_last_received_anchor) CT ON CT.[CustomerId] = f.[CustomerId]
      WHERE f.City = @city AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)
     ELSE
     BEGIN
      SELECT f.*
      FROM dbo.Customers f
      INNER JOIN CHANGETABLE(CHANGES dbo.Customers, @sync_last_received_anchor) CT ON CT.[CustomerId] = f.[CustomerId]
      WHERE f.City = @city AND (CT.SYS_CHANGE_OPERATION = ''I'' AND CT.SYS_CHANGE_CREATION_VERSION <= @sync_new_received_anchor
       AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary));
       
      IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) > @sync_last_received_anchor
       RAISERROR (N''SQL Server Change Tracking has cleaned up tracking information for table ''''%s''''. To recover from this error, the client must reinitialize its local database and try again'', 16, 3, @sync_table_name);
     END
    END


    For more details please read the section "How to: Filter Rows and Columns" in the SDK documentation.
    Thursday, July 23, 2009 10:51 PM
  • hi,

    use the following link for filtering the data.

    http://msdn.microsoft.com/en-us/library/bb726003.aspx

    hope it will hel you....................
    Friday, July 24, 2009 5:55 AM
  • In additional to What Bob has suggests, you also need to add the @city as a sync parameter in the SyncAgent.Configuration.SyncParameters.

    Thanks.
    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, July 24, 2009 5:30 PM
    Answerer