locked
Changes on server after upload not downloading to client RRS feed

  • Question

  • Hi, i've got a table configured for bi-directional sync. The table uses DATETIME fields to track created/updated rows. When my client (Windows Mobile device) uploads changes to the server, I run some business logic within the ChangesApplied event on the server. This business logic sends off some email/SMS alerts, modifies the uploaded row to say that alerts were sent successfully and also updates the DateUpdated to flag-up that a change has been made to the row (by the server). The sync then completes successfully (all uploaded changes applied) However, the changes in this uploaded row (performed by the server during the sync) are not being downloaded to the client after the sync completes. The device is not downloading any changes made on the server during the sync and in subsequent syncs these changes are also not picked up by the client (even though the 'date updated' field is different on server and client). I'm just wondering if I am doing anything wrong here? Any pointers appreciated as to work-arounds...
    • Moved by Hengzhe Li Friday, April 22, 2011 2:51 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Wednesday, July 1, 2009 4:10 PM

Answers

  • During the sync process, the server inserts the new row from the client:
    INSERT INTO dbo.Incidents ([IncidentUID], [CreatedBy_UserID], [UpdatedBy_UserID], [AreaTaskID], [ActionID], [ActionTimeID], [AssignedTo_UserID], [DateCreated], [DateIssued], [DateActioned], [DateCompleted], [DateDue], [DateUpdated]) VALUES (@IncidentUID, @CreatedBy_UserID, @UpdatedBy_UserID, @AreaTaskID, @ActionID, @ActionTimeID, @AssignedTo_UserID, @DateCreated, @DateIssued, @DateActioned, @DateCompleted, @DateDue, @DateUpdated) 
    

    During the sync (in the ChangesApplied event handler), this row is modified by a stored procedure:

    UPDATE Incidents
    	SET UpdatedBy_UserID = @CreatedBy_UserID,
    		AssignedTo_UserID = @AssignedTo_UserID,
    		DateIssued = GETDATE(),
    	    	DateUpdated = GETDATE()
    	WHERE IncidentUID = @IncidentUID

    The server then selects rows to send back to the client (?) (this is what I am inferring):

    Select @sync_new_received_anchor = GETUTCDATE()
    
    SELECT [IncidentUID], [CreatedBy_UserID], [UpdatedBy_UserID], [AreaTaskID], [ActionID], [ActionTimeID], [AssignedTo_UserID], [DateCreated], [DateIssued], [DateActioned], [DateCompleted], [DateDue], [DateUpdated] FROM dbo.Incidents WHERE ([DateCreated] > @sync_last_received_anchor AND [DateCreated] <= @sync_new_received_anchor)
    go
    
    SELECT [IncidentUID], [CreatedBy_UserID], [UpdatedBy_UserID], [AreaTaskID], [ActionID], [ActionTimeID], [AssignedTo_UserID], [DateCreated], [DateIssued], [DateActioned], [DateCompleted], [DateDue], [DateUpdated] FROM dbo.Incidents WHERE ([DateCreated] > @sync_last_received_anchor AND [DateCreated] <= @sync_new_received_anchor)
    go
    
    SELECT [IncidentUID], [CreatedBy_UserID], [UpdatedBy_UserID], [AreaTaskID], [ActionID], [ActionTimeID], [AssignedTo_UserID], [DateCreated], [DateIssued], [DateActioned], [DateCompleted], [DateDue], [DateUpdated] FROM dbo.Incidents WHERE ([DateUpdated] > @sync_last_received_anchor AND [DateUpdated] <= @sync_new_received_anchor AND [DateCreated] <= @sync_last_received_anchor)
    go
    
    SELECT [IncidentUID], [CreatedBy_UserID], [UpdatedBy_UserID], [AreaTaskID], [ActionID], [ActionTimeID], [AssignedTo_UserID], [DateCreated], [DateIssued], [DateActioned], [DateCompleted], [DateDue], [DateUpdated] FROM dbo.Incidents WHERE ([DateUpdated] > @sync_last_received_anchor AND [DateUpdated] <= @sync_new_received_anchor AND [DateCreated] <= @sync_last_received_anchor)
    go
    
    SELECT [IncidentUID], [DeletionDate] FROM dbo.Incidents_Tombstone WHERE (@sync_initialized = 1 AND [DeletionDate] > @sync_last_received_anchor AND [DeletionDate] <= @sync_new_received_anchor)
    go
    
    SELECT [IncidentUID], [DeletionDate] FROM dbo.Incidents_Tombstone WHERE (@sync_initialized = 1 AND [DeletionDate] > @sync_last_received_anchor AND [DeletionDate] <= @sync_new_received_anchor)
    go
    
    

    From the above, the only elements that seem to matter when selecting rows are @sync_last_received_anchor and @sync_new_received_anchor. I'm not sure where the ClientID fits into things when selecting records? (certainly nothing in the SQL statements referring to it).

    EDIT: Just noticed that
    @sync_new_received_anchor = GETUTCDATE()

     GETUTCDATE() gives a different result to GETDATE() (the default value of new rows in my table). This could be the issue here....
    • Edited by SunHunter Thursday, July 2, 2009 10:53 AM
    • Marked as answer by SunHunter Thursday, July 2, 2009 1:07 PM
    Thursday, July 2, 2009 10:43 AM
  • Typically with bi-di sync you need some logic to handle conflicts when you apply changes and loop backs when you get changes.  The commands below seem to be missing that logic so you might want to take a look at the how to below:

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

    I pulled the following command from that sample:

    SqlCommand customerIncrUpdates = new SqlCommand();

    customerIncrUpdates.CommandText =

        "SELECT CustomerId, CustomerName, SalesPerson, CustomerType " +

        "FROM Sales.Customer " +

        "WHERE (UpdateTimestamp > @sync_last_received_anchor " +

        "AND UpdateTimestamp <= @sync_new_received_anchor " +

        "AND UpdateId <> @sync_client_id " +

        "AND NOT (InsertTimestamp > @sync_last_received_anchor " +

        "AND InsertId <> @sync_client_id))";

    customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);

    customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);

    customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);

    customerIncrUpdates.Connection = serverConn;

    customerSyncAdapter.SelectIncrementalUpdatesCommand = customerIncrUpdates;


    UpdateId <> @sync_client_id  and InsertId <> @sync_client_id  essentially say do not retiereve any changes from me where me is the client.  The default workflow gets changes from the client, applies them on the server, gets changes from the server and applies them on the client.  As such, I generally do not want to get the changes that I just applied and these predicated handle that case.

    Before drilling into this further, my recommendation would be to leverage the how to noted above to update your commands for correctness.  The change application commands should also be checking for conflicts.  After doing so, this may resolve your issue.  Note that you could also use SyncAdapterBuilder to generate these commands for you to get you started.  Other option would be to leverage the “Local Database Cache” that ships with Visual Studio 2008.  This is a designer that will generate the commands for you as well.

    Regards,

    Sean Kelley
    Program Manager
    Microsoft

    Thursday, July 2, 2009 8:33 PM
    Moderator

All replies

  • Before sync:

    IncidentUID = 2763821e-5396-4075-bf06-c8b365a0cae4
    CreatedBy_UserID = 189
    AreaTaskID = 66552
    ActionID = 4
    ActionTimeID = 312
    AssignedTo_UserID = 1
    DateUpdated = 01/07/09 18:54:49
    DateDue = 02/07/09 18:55:12
    DateIssued = NULL

    After sync, data on server is as follows:

    IncidentUID = 2763821e-5396-4075-bf06-c8b365a0cae4
    CreatedBy_UserID = 189
    AreaTaskID = 66552
    ActionID = 4
    ActionTimeID = 312
    AssignedTo_UserID = 1
    DateUpdated = 01/07/09 18:56:36 *** this column has changed
    DateDue = 02/07/09 18:55:12
    DateIssued = 2009-07-01 18:56:36 *** this column has changed


    So, I'd then expect the modified record to be downloaded to device:

    DownloadChangesApplied = 0
    DownloadChangesFailed = 0

    .... but the data on the device after the sync is as per what was originally uploaded.
    Wednesday, July 1, 2009 6:05 PM
  • Sun,

    Could you run sql profiler and take a look at the queries being executed.  Perhaps that might give some indication as to why these changes are not being downloaded.  Incidentally, it could be because the record is being filtered out as a loopback so you might want to also set the client id to indicate that the change was made by the server and all clients should have access to these changes.

    Regards,

    Sean Kelley
    Program Manager
    Microsoft
    Thursday, July 2, 2009 12:23 AM
    Moderator
  • Hi, many thanks for taking the time to respond. Your answer is very helpful and I think is putting me on the right track. I'm not sure how to set the ClientID to indicate a change by the server. If my code overrides some values in the ChangesApplied event (for a subset of rows), how/where do I set the ClientID for those rows that have been modified by the server?

    I've looked through MSDN and found only this article on SelectClientIdCommand: http://msdn.microsoft.com/en-us/library/microsoft.synchronization.data.server.dbserversyncprovider.selectclientidcommand.aspx but it doesn't seem to explain if you can override the clientid for a particular subset of rows (it's only one or two tables in the syncgroup that needs to be modified by the server), or how to set to the ClientID to indicate a change by the server. Apologies for taking your time up, or if I am misunderstanding. I do appreciate you taking the time to give your initial answer.

    e.g.
    private void ScrutinizeLiveDataCacheServerSyncProvider_ChangesApplied(object sender, ChangesAppliedEventArgs e)
            {
                if (e.GroupMetadata.GroupName == "grpIncidents")
                {
                    if (this._UploadedIncidents.Count > 0)
                    {
                        ScrutinizeLiveWeb.CIncidentStatusIssue Issue = new ScrutinizeLiveWeb.CIncidentStatusIssue();
                       
                        for (int x = 0; x < this._UploadedIncidents.Count; x++)
                        {
                            Issue.Incident_Issue(UploadedIncident, UploadedIncident._AssignedTo_User, UploadedIncident._CreatedBy_User, OriginalIncident, (SqlTransaction)e.Transaction, (SqlConnection)e.Connection); // **** business logic here issues helpdesk ticket and updates table ***** but where to set ClientID for this row?
    
                        } // end loop through/issue uploaded incidents
                    } // end if uploaded incidents exist
                } // end if grpIncidents
            }
    Thursday, July 2, 2009 8:44 AM
  • During the sync process, the server inserts the new row from the client:
    INSERT INTO dbo.Incidents ([IncidentUID], [CreatedBy_UserID], [UpdatedBy_UserID], [AreaTaskID], [ActionID], [ActionTimeID], [AssignedTo_UserID], [DateCreated], [DateIssued], [DateActioned], [DateCompleted], [DateDue], [DateUpdated]) VALUES (@IncidentUID, @CreatedBy_UserID, @UpdatedBy_UserID, @AreaTaskID, @ActionID, @ActionTimeID, @AssignedTo_UserID, @DateCreated, @DateIssued, @DateActioned, @DateCompleted, @DateDue, @DateUpdated) 
    

    During the sync (in the ChangesApplied event handler), this row is modified by a stored procedure:

    UPDATE Incidents
    	SET UpdatedBy_UserID = @CreatedBy_UserID,
    		AssignedTo_UserID = @AssignedTo_UserID,
    		DateIssued = GETDATE(),
    	    	DateUpdated = GETDATE()
    	WHERE IncidentUID = @IncidentUID

    The server then selects rows to send back to the client (?) (this is what I am inferring):

    Select @sync_new_received_anchor = GETUTCDATE()
    
    SELECT [IncidentUID], [CreatedBy_UserID], [UpdatedBy_UserID], [AreaTaskID], [ActionID], [ActionTimeID], [AssignedTo_UserID], [DateCreated], [DateIssued], [DateActioned], [DateCompleted], [DateDue], [DateUpdated] FROM dbo.Incidents WHERE ([DateCreated] > @sync_last_received_anchor AND [DateCreated] <= @sync_new_received_anchor)
    go
    
    SELECT [IncidentUID], [CreatedBy_UserID], [UpdatedBy_UserID], [AreaTaskID], [ActionID], [ActionTimeID], [AssignedTo_UserID], [DateCreated], [DateIssued], [DateActioned], [DateCompleted], [DateDue], [DateUpdated] FROM dbo.Incidents WHERE ([DateCreated] > @sync_last_received_anchor AND [DateCreated] <= @sync_new_received_anchor)
    go
    
    SELECT [IncidentUID], [CreatedBy_UserID], [UpdatedBy_UserID], [AreaTaskID], [ActionID], [ActionTimeID], [AssignedTo_UserID], [DateCreated], [DateIssued], [DateActioned], [DateCompleted], [DateDue], [DateUpdated] FROM dbo.Incidents WHERE ([DateUpdated] > @sync_last_received_anchor AND [DateUpdated] <= @sync_new_received_anchor AND [DateCreated] <= @sync_last_received_anchor)
    go
    
    SELECT [IncidentUID], [CreatedBy_UserID], [UpdatedBy_UserID], [AreaTaskID], [ActionID], [ActionTimeID], [AssignedTo_UserID], [DateCreated], [DateIssued], [DateActioned], [DateCompleted], [DateDue], [DateUpdated] FROM dbo.Incidents WHERE ([DateUpdated] > @sync_last_received_anchor AND [DateUpdated] <= @sync_new_received_anchor AND [DateCreated] <= @sync_last_received_anchor)
    go
    
    SELECT [IncidentUID], [DeletionDate] FROM dbo.Incidents_Tombstone WHERE (@sync_initialized = 1 AND [DeletionDate] > @sync_last_received_anchor AND [DeletionDate] <= @sync_new_received_anchor)
    go
    
    SELECT [IncidentUID], [DeletionDate] FROM dbo.Incidents_Tombstone WHERE (@sync_initialized = 1 AND [DeletionDate] > @sync_last_received_anchor AND [DeletionDate] <= @sync_new_received_anchor)
    go
    
    

    From the above, the only elements that seem to matter when selecting rows are @sync_last_received_anchor and @sync_new_received_anchor. I'm not sure where the ClientID fits into things when selecting records? (certainly nothing in the SQL statements referring to it).

    EDIT: Just noticed that
    @sync_new_received_anchor = GETUTCDATE()

     GETUTCDATE() gives a different result to GETDATE() (the default value of new rows in my table). This could be the issue here....
    • Edited by SunHunter Thursday, July 2, 2009 10:53 AM
    • Marked as answer by SunHunter Thursday, July 2, 2009 1:07 PM
    Thursday, July 2, 2009 10:43 AM
  • Typically with bi-di sync you need some logic to handle conflicts when you apply changes and loop backs when you get changes.  The commands below seem to be missing that logic so you might want to take a look at the how to below:

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

    I pulled the following command from that sample:

    SqlCommand customerIncrUpdates = new SqlCommand();

    customerIncrUpdates.CommandText =

        "SELECT CustomerId, CustomerName, SalesPerson, CustomerType " +

        "FROM Sales.Customer " +

        "WHERE (UpdateTimestamp > @sync_last_received_anchor " +

        "AND UpdateTimestamp <= @sync_new_received_anchor " +

        "AND UpdateId <> @sync_client_id " +

        "AND NOT (InsertTimestamp > @sync_last_received_anchor " +

        "AND InsertId <> @sync_client_id))";

    customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);

    customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);

    customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);

    customerIncrUpdates.Connection = serverConn;

    customerSyncAdapter.SelectIncrementalUpdatesCommand = customerIncrUpdates;


    UpdateId <> @sync_client_id  and InsertId <> @sync_client_id  essentially say do not retiereve any changes from me where me is the client.  The default workflow gets changes from the client, applies them on the server, gets changes from the server and applies them on the client.  As such, I generally do not want to get the changes that I just applied and these predicated handle that case.

    Before drilling into this further, my recommendation would be to leverage the how to noted above to update your commands for correctness.  The change application commands should also be checking for conflicts.  After doing so, this may resolve your issue.  Note that you could also use SyncAdapterBuilder to generate these commands for you to get you started.  Other option would be to leverage the “Local Database Cache” that ships with Visual Studio 2008.  This is a designer that will generate the commands for you as well.

    Regards,

    Sean Kelley
    Program Manager
    Microsoft

    Thursday, July 2, 2009 8:33 PM
    Moderator
  • Thanks very much for your help. I now have something working :) My next step will be to to start optimizing performance, so you may see me back here pretty soon. The issue I had was to do with GETUTCDATE() being used by sync services to compare updates/inserts but my database table was using GETDATE() to populate default values for those columns (I only noticed this when doing the db trace like you initially suggested, so thanks again).
    Friday, July 3, 2009 1:59 PM