none
RemoteSqlProvider.ApplyChanges not working RRS feed

  • Question

  • Hi,

    I am having some trouble with the RemoteSqlProvider.ApplyChanges method. It should be inserting two records but it is not. When debug I verified that it is being passed two new records in the DataSet, and I verified it was working on the right connection. Everything seems ok but no insert command is being sent to the database. Also it is updating the anchor so it seems to think it inserted the rows. The only clue I have is that this particular table is SyncDirection.DownloadOnly and when I change it to BiDirectional it works. Btw this is using Sql 2008 change tracking for versioning.

    Thanks
    * Note: I have no formal CS education, so take all advice with caution :)
    Monday, March 15, 2010 4:15 PM

Answers

  • After digging through the microsoft sync code using reflector I figured out it's becasue the DbServerSyncProvider does not support a DownloadOnly Sync. I am using the SQL Express provider which wraps the DbServerSyncProvider, so like the GetChanges method I just swapped the upload with download SyncDirections and it seems to work ok: (needs refactoring)

                /// <summary>
                /// Apply changes downloaded from the server. 
                /// </summary>
                /// <remarks>
                /// Inner _dbSyncProvider will take care of applying changes to actual 
                /// data, but we need to take care of updating anchor metadata. 
                /// </remarks>
                /// <param name="groupMetadata"> Contains table metadata info </param>
                /// <param name="dataSet"> Contains changes to be applied </param>
                /// <param name="syncSession"> Current sync session </param>
                /// <returns> SyncContext object to Sync Agent </returns>
                public override SyncContext ApplyChanges(SyncGroupMetadata groupMetadata, DataSet dataSet, SyncSession syncSession)
                {
                    // Need to flip download and upload SyncDirections 
                    // since this is from the server perspective
                    foreach (SyncTableMetadata metaTable in groupMetadata.TablesMetadata)
                    {
                        switch (metaTable.SyncDirection)
                        {
                            case SyncDirection.DownloadOnly:
                            {
                                metaTable.SyncDirection = SyncDirection.UploadOnly;
                                break;
                            }
                            case SyncDirection.UploadOnly:
                            {
                                metaTable.SyncDirection = SyncDirection.DownloadOnly;
                                break;
                            }
                        }
                    }
    
                    SyncContext syncContext = _remoteSqlProvider.ApplyChanges(groupMetadata, dataSet, syncSession);
    
                    // Swap them back for consistency
                    foreach (SyncTableMetadata metaTable in groupMetadata.TablesMetadata)
                    {
                        switch (metaTable.SyncDirection)
                        {
                            case SyncDirection.DownloadOnly:
                                {
                                    metaTable.SyncDirection = SyncDirection.UploadOnly;
                                    break;
                                }
                            case SyncDirection.UploadOnly:
                                {
                                    metaTable.SyncDirection = SyncDirection.DownloadOnly;
                                    break;
                                }
                        }
                    }
    
                    // Set table rec'd anchors
                    foreach (SyncTableMetadata table in groupMetadata.TablesMetadata)
                    {
                        SetTableReceivedAnchor(table.TableName, groupMetadata.NewAnchor);
                    }
                    return syncContext;
    
                }

    * Note: I have no formal CS education, so take all advice with caution :)
    • Marked as answer by EisenB Monday, March 15, 2010 6:17 PM
    Monday, March 15, 2010 6:17 PM

All replies

  • If it helps, here is the SyncTrace log:

    VERBOSE, POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:187, StoreInfo: Read SentAnchor value: NULL
    VERBOSE, POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:187, StoreInfo: Read ReceivedAnchor value: NULL
    VERBOSE, POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:218, Connecting using string: Data Source=LOCALHOST\SQLEXPRESS;Initial Catalog=HomeOfficePOS;Integrated Security=True
    INFO   , POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:234, ----- Server Enumerating Changes to Client for Group "TestGroup" -----
    INFO   , POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:265,               Client Id: 1317a939-5632-49b0-a15a-6772606fae59
    INFO   , POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:265,    Mapped Originator Id: 0
    VERBOSE, POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:281, Using Command: SELECT @sync_new_received_anchor = change_tracking_current_version()
    VERBOSE, POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:281,    Parameter: @sync_new_received_anchor Value: Skipped since Not Input/InputOutput
    VERBOSE, POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:281, 
    INFO   , POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:296, 
    INFO   , POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:296,    ----- Enumerating Inserts for Table StoreInfo -----
    VERBOSE, POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:296, Using Command: IF @sync_initialized = 0 SELECT A.StoreID, A.StoreGuid, A.StoreName FROM dbo.StoreInfo A LEFT OUTER JOIN CHANGETABLE(CHANGES dbo.StoreInfo, @sync_last_received_anchor) CT ON CT.StoreID = A.StoreID WHERE (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary) ELSE  BEGIN SELECT A.StoreID, A.StoreGuid, A.StoreName FROM dbo.StoreInfo A JOIN CHANGETABLE(CHANGES dbo.StoreInfo, @sync_last_received_anchor) CT ON CT.StoreID = A.StoreID 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
    VERBOSE, POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:296,    Parameter: @sync_initialized Value: 0
    VERBOSE, POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:296,    Parameter: @sync_last_received_anchor Value: 0
    VERBOSE, POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:312,    Parameter: @sync_client_id_binary Len: 16 Value: 39-A9-17-13-32-56-B0-49-A1-5A-67-72-60-6F-AE-59
    VERBOSE, POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:312,    Parameter: @sync_new_received_anchor Value: 1787
    VERBOSE, POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:312,    Parameter: @sync_table_name Len: 9 Value: StoreInfo
    VERBOSE, POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:312, 
    INFO   , POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:312,       Changes Enumerated: 2
    VERBOSE, POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:312,       Insert for row with PK: StoreID="1" 
    VERBOSE, POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:312,       Insert for row with PK: StoreID="2" 
    INFO   , POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:327,    --- End Enumerating Inserts for Table StoreInfo ---
    INFO   , POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:327, 
    INFO   , POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:327,    ----- Enumerating Updates for Table StoreInfo -----
    VERBOSE, POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:327, Using Command: IF @sync_initialized > 0  BEGIN SELECT A.StoreID, A.StoreGuid, A.StoreName FROM dbo.StoreInfo A JOIN CHANGETABLE(CHANGES dbo.StoreInfo, @sync_last_received_anchor) CT ON CT.StoreID = A.StoreID WHERE (CT.SYS_CHANGE_OPERATION = 'U' AND CT.SYS_CHANGE_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
    VERBOSE, POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:327,    Parameter: @sync_initialized Value: 0
    VERBOSE, POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:327,    Parameter: @sync_last_received_anchor Value: 0
    VERBOSE, POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:327,    Parameter: @sync_new_received_anchor Value: 1787
    VERBOSE, POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:327,    Parameter: @sync_client_id_binary Len: 16 Value: 39-A9-17-13-32-56-B0-49-A1-5A-67-72-60-6F-AE-59
    VERBOSE, POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:327,    Parameter: @sync_table_name Len: 9 Value: StoreInfo
    VERBOSE, POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:327, 
    INFO   , POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:327,       Changes Enumerated: 0
    INFO   , POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:327,    --- End Enumerating Updates for Table StoreInfo ---
    INFO   , POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:327, 
    INFO   , POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:327,    ----- Enumerating Deletes for Table StoreInfo -----
    VERBOSE, POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:343, Using Command: IF @sync_initialized > 0  BEGIN SELECT CT.StoreID FROM CHANGETABLE(CHANGES dbo.StoreInfo, @sync_last_received_anchor) CT WHERE (CT.SYS_CHANGE_OPERATION = 'D' AND CT.SYS_CHANGE_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
    VERBOSE, POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:343,    Parameter: @sync_initialized Value: 0
    VERBOSE, POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:343,    Parameter: @sync_last_received_anchor Value: 0
    VERBOSE, POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:343,    Parameter: @sync_new_received_anchor Value: 1787
    VERBOSE, POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:343,    Parameter: @sync_client_id_binary Len: 16 Value: 39-A9-17-13-32-56-B0-49-A1-5A-67-72-60-6F-AE-59
    VERBOSE, POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:343,    Parameter: @sync_table_name Len: 9 Value: StoreInfo
    VERBOSE, POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:343, 
    INFO   , POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:343,       Changes Enumerated: 0
    INFO   , POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:343,    --- End Enumerating Deletes for Table StoreInfo ---
    INFO   , POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:343, --- End Server Enumerating Changes from Client for Group "TestGroup" ---
    INFO   , POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:343, Closing connection to server
    INFO   , POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:359, ----- Server Applying Changes from Client for Group "TestGroup" -----
    INFO   , POSPollingPOC.vshost, 9, 03/15/2010 17:20:40:359, --- End Server Applying Changes from Client for Group "TestGroup" ---
    

    * Note: I have no formal CS education, so take all advice with caution :)
    Monday, March 15, 2010 5:21 PM
  • After digging through the microsoft sync code using reflector I figured out it's becasue the DbServerSyncProvider does not support a DownloadOnly Sync. I am using the SQL Express provider which wraps the DbServerSyncProvider, so like the GetChanges method I just swapped the upload with download SyncDirections and it seems to work ok: (needs refactoring)

                /// <summary>
                /// Apply changes downloaded from the server. 
                /// </summary>
                /// <remarks>
                /// Inner _dbSyncProvider will take care of applying changes to actual 
                /// data, but we need to take care of updating anchor metadata. 
                /// </remarks>
                /// <param name="groupMetadata"> Contains table metadata info </param>
                /// <param name="dataSet"> Contains changes to be applied </param>
                /// <param name="syncSession"> Current sync session </param>
                /// <returns> SyncContext object to Sync Agent </returns>
                public override SyncContext ApplyChanges(SyncGroupMetadata groupMetadata, DataSet dataSet, SyncSession syncSession)
                {
                    // Need to flip download and upload SyncDirections 
                    // since this is from the server perspective
                    foreach (SyncTableMetadata metaTable in groupMetadata.TablesMetadata)
                    {
                        switch (metaTable.SyncDirection)
                        {
                            case SyncDirection.DownloadOnly:
                            {
                                metaTable.SyncDirection = SyncDirection.UploadOnly;
                                break;
                            }
                            case SyncDirection.UploadOnly:
                            {
                                metaTable.SyncDirection = SyncDirection.DownloadOnly;
                                break;
                            }
                        }
                    }
    
                    SyncContext syncContext = _remoteSqlProvider.ApplyChanges(groupMetadata, dataSet, syncSession);
    
                    // Swap them back for consistency
                    foreach (SyncTableMetadata metaTable in groupMetadata.TablesMetadata)
                    {
                        switch (metaTable.SyncDirection)
                        {
                            case SyncDirection.DownloadOnly:
                                {
                                    metaTable.SyncDirection = SyncDirection.UploadOnly;
                                    break;
                                }
                            case SyncDirection.UploadOnly:
                                {
                                    metaTable.SyncDirection = SyncDirection.DownloadOnly;
                                    break;
                                }
                        }
                    }
    
                    // Set table rec'd anchors
                    foreach (SyncTableMetadata table in groupMetadata.TablesMetadata)
                    {
                        SetTableReceivedAnchor(table.TableName, groupMetadata.NewAnchor);
                    }
                    return syncContext;
    
                }

    * Note: I have no formal CS education, so take all advice with caution :)
    • Marked as answer by EisenB Monday, March 15, 2010 6:17 PM
    Monday, March 15, 2010 6:17 PM
  • Hi Eisen,

    Did you use the sample SQLExpress Client Provider  as the remote provider?

    JuneT
    Tuesday, March 16, 2010 2:41 AM
    Moderator
  • The remote provider is derived from a DbServerSyncProvider. The local provider is the sql express provider which wraps the remote provider.


    * Note: I have no formal CS education, so take all advice with caution :)
    Wednesday, March 17, 2010 4:49 PM
  • Hi,

    Im using http://blogs.msdn.com/b/sync/archive/2008/06/24/sample-sql-express-client-synchronization-using-sync-services-for-ado-net.aspx link to Sync between SQL Server n SQL server Express. BUT IM USING SQL SERVER EXPRESS FOR BOTH CLIENT N SERVER PROVIDER. I've the prob with DownLoadOnlySyncDirection. Its not working.

    Now I want to solve my DownLoad only problem. thanks

    Tuesday, August 17, 2010 6:05 AM
  • Hi, this link solve my prob, http://social.msdn.microsoft.com/forums/en-US/uklaunch2007ado.net/thread/9e65722a-cdb7-46e2-8a44-c6b466ed873b/
    Tuesday, August 17, 2010 6:24 AM