locked
Device Project - Download table contents on first sync RRS feed

  • Question

  •  

    Hi

     

    I'm working my way through the Mobile Line of Business HardwareDistributor example, and am at the stage of wanting to use my own tables for syncing.

     

    I'm using SQL Server 2008, and it appears that data for a particular table is only included for download if it appears in the as a result of the ChangeTable function, ie. it has been inserted/amended since tracking was turned on for that table. I have a number of existing rows that will never be updated, therefore the rows won't appear in the tracking table and won't be included on the server - client insert. Is there a simple mechanism within Sync Services that will detect whether this is the first sync or a particular table and if so to download the entire contents? I'd like to ensure that on installation of my application on a device, that a number of lookup tables will be downloaded to the device on first sync.

     

    There are a number of threads similar to this, such as http://forums.microsoft.com/Sync/ShowPost.aspx?PostID=3729457&SiteID=75 but no difinitive answer as yet as far as I can see.

     

    Regards

    Matt

    • Moved by Max Wang_1983 Friday, April 22, 2011 5:08 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Thursday, August 14, 2008 2:31 PM

Answers

  • If you use SqlSyncAdapterBuilder, and use the code sample I have provided, you can see the generated SelectIncrementalInsertCommand text is

     

    IF @sync_initialized = 0 SELECT [bar].[k1], [c2] FROM [bar] LEFT OUTER JOIN CHANGETABLE(CHANGES [bar], @sync_last_received_anchor) CT ON CT.[k1] = [bar].[k1] WHERE (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary) ELSE  BEGIN SELECT [bar].[k1], [c2] FROM [bar] JOIN CHANGETABLE(CHANGES [bar], @sync_last_received_anchor) CT ON CT.[k1] = [bar].[k1] WHERE (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

     

    Short explanation -

    If this is an initial sync, @sync_initialized is assigned to 0.  And we do a left outer join between the user table [bar] and the change tracking table CHANGETABLE().  So any rows not in the CHANGETABLE() will be selected as well.

     

    You can give a try.

     

    Thanks

    Tuesday, September 2, 2008 5:50 PM
    Answerer

All replies

  • Hello Matt,

     

    http://forums.microsoft.com/Sync/ShowPost.aspx?PostID=3729457&SiteID=75 is regarding how to detect initial sync on device from the client point of view.

     

    What you need here is to get rows from the server, therefore, you need to include @sync_initialized in the SelectIncrementalInsertCommand.  When "@sync_initialized = 0", it means the first sync for this client device.

     

    You can also build the sync adapter using the adapter builder in a simple way.  The adapter builder will take care of including the @sync_initialized in the command for your.  The below lines of code is just a sample.  Please use it in a way that fits your business scenario.

     

    SqlSyncAdapterBuilder builder = new SqlSyncAdapterBuilder(conn);

    builder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;

    builder.TableName = "bar";

    builder.SyncDirection = SyncDirection.Bidirectional;

    builder.DataColumns.Add("k1");

    builder.DataColumns.Add("c2");

    SyncAdapter adapter = builder.ToSyncAdapter();

    serverProvider.SyncAdapters.Add(adapter);

     

    Thanks.

    Thursday, August 14, 2008 4:23 PM
    Answerer
  • Thanks for the reply L Zhou.

    I'm still confused though, surely the client has to tell the server that it requires all data for downloadonly and bidirectional tables? It would make sense to me if I could test whether a client sdf/table has been synced previously in client side code, then request relevant data from the server database? As far as I can see it is the service only that decides what data to send back to the client, as in this example from the LOB Solution Accelerator web service. Can I somehow tell the service this is first sync, then set the InsertCommand in the SyncAdapter to return all rows as appropriate?

     private SyncAdapter GetDownloadOnlyTableAdapter(string tableName, string idColumnName, string[] columnsToSync)
            {
                //name it same as the table
                SyncAdapter syncAdapter = new SyncAdapter(tableName);

                //get string that can be used in a sql statement
                string columnString = GetColumnsString(columnsToSync, "originalTable");


                //select inserts from the server SQL Server 2008
                SqlCommand incrementalInsertCommand = new SqlCommand();
                incrementalInsertCommand.CommandText = String.Format(
                                @"SELECT {0} FROM {1} as originalTable
                                INNER JOIN ChangeTable(changes {1}, {2} ) ct
                                ON ct.{3} = originalTable.{3}
                                where ct.SYS_CHANGE_OPERATION = '{4}'
                                ", columnString , tableName, "@" + SyncSession.SyncLastReceivedAnchor,idColumnName, OPERATION_INSERT);
                incrementalInsertCommand.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt);
                syncAdapter.SelectIncrementalInsertsCommand = incrementalInsertCommand;
               
    return syncAdapter;
            }

    All that happens here is to grab changes from the SQL Server 2008 ChangeTable, this will not retrieve data that doesn't appear in the changes table - for instance, any data that not changed since Change Tracking was enabled. 

    I'd really appreciate some clarification on this issue, as at the moment the only way I can see to download data to a new device is to change all records in a dummy column on my tables!
    Tuesday, September 2, 2008 3:11 PM
  • If you use SqlSyncAdapterBuilder, and use the code sample I have provided, you can see the generated SelectIncrementalInsertCommand text is

     

    IF @sync_initialized = 0 SELECT [bar].[k1], [c2] FROM [bar] LEFT OUTER JOIN CHANGETABLE(CHANGES [bar], @sync_last_received_anchor) CT ON CT.[k1] = [bar].[k1] WHERE (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary) ELSE  BEGIN SELECT [bar].[k1], [c2] FROM [bar] JOIN CHANGETABLE(CHANGES [bar], @sync_last_received_anchor) CT ON CT.[k1] = [bar].[k1] WHERE (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

     

    Short explanation -

    If this is an initial sync, @sync_initialized is assigned to 0.  And we do a left outer join between the user table [bar] and the change tracking table CHANGETABLE().  So any rows not in the CHANGETABLE() will be selected as well.

     

    You can give a try.

     

    Thanks

    Tuesday, September 2, 2008 5:50 PM
    Answerer
  • Thanks L Zhou, that did work. As I am only trying to modify the LOB example I'm not sure why the same SelectIncrementalInsertCommand didn't work for my tables.

    I have a new issue regarding the SqlSyncAdapterBuilder, which is that it is not inserting new rows from the client to the server. The
    InsertCommand text appears to be set correctly by the builder, however when I synchronize and run a profile on the SQL server the Insert statement doesn't show and the new record remains on the client.

    The ServerProvider code is as follows:
    private DbServerSyncProvider _serverSyncProvider;
    SqlSyncAdapterBuilder CRTBuilder = new SqlSyncAdapterBuilder(serverConnection);
    CRTBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;
    CRTBuilder.TableName = "tblCRT";
    CRTBuilder.SyncDirection = SyncDirection.UploadOnly;
    SyncAdapter CRTAdapter = CRTBuilder.ToSyncAdapter();
    _serverSyncProvider.SyncAdapters.Add(CRTAdapter);

    The ClientAgent code as follows:

    sealed class ClientSyncAgent : SyncAgent...

    private ClientSyncAgent()
            {

    SyncGroup uberGroup = new SyncGroup("UberGroup");
                SyncTable CRTSyncTable = new SyncTable("tblCRT");
                CRTSyncTable.CreationOption = TableCreationOption.UploadExistingOrCreateNewTable;
                CRTSyncTable.SyncDirection = SyncDirection.UploadOnly;
                CRTSyncTable.SyncGroup = uberGroup;
                this.Configuration.SyncTables.Add(CRTSyncTable);

    Sync is successful, but if I create a SyncStatistics object it tells me UploadChangesFailed  = 1 but not why it failed.

    Thanks
    Matt
    Tuesday, September 16, 2008 2:25 PM
  • OK, getting somewhere with this now I think, using the syncbuilder to build my insert statement I managed to sync a new record from client to server. I notice that the SQL InsertCommand generated by the builder includes SET IDENTITY_INSERT [tblCRT] ON and specifies the ID column value to be the same as in the client database. Is this because I should be using GUIDs as PK on a table? I'm using a legacy system and this is not an option for me. I presume to get the newly inserted row from the client to autoincrement as per the server table schema I need to manually specify the InsertCommand without IDENTITY_INSERT and not provide the ID column value?

    As the first client ID is 0 and the the server is set to increment from 1, the insert is valid and executes fine, however, the SyncStatistics object still reports a failed insert even though the record was inserted, and the record is left on the device.

    If I capture the insert statement and run it manually I receive the following messages as output:

    (1 row(s) affected)
    Msg 50000, Level 16, State 3, Line 1
    SQL Server Change Tracking has cleaned up tracking information for table '[tblCRT]'. To recover from this error, the client must reinitialize its local database and try again.

    Any suggestions more than welcome!
     
    Tuesday, September 16, 2008 4:07 PM
  • OK, it appears that this error is because the @sync_last_received_anchor value is 0 and the SQL statement generated by the builder includes the following, hence the error:

    IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'[tblCRT]')) > @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,N'[tblCRT]')

    The client database has been synced, so I don't understand why the
    @sync_last_received_anchor value is 0?
    Wednesday, September 17, 2008 11:21 AM