locked
Client Db not Updating RRS feed

  • Question

  • Hi,

     

    I am new to sync services and have a couple questions.  I am really struggling with the anchors that the client is using to perform updates and such.

     

    Background:

    I am using a SqlExpressClientSyncProvider class that I found online.

    User's of the system will vpn in when they synchronize; therefore, I will have direct access to the database via an ip address (not using a web service).

    I'm using sql express on the client and sql 2000 on the server.

    I'm using the "orders"/"order_details" sample to cut my teeth on and keep it simple; therefore, my "anchor" table (on the client) has a row for each of these tables.  The "anchor" table also has two additional columns, "sentanchor" and "receiveanchor".

     

    Scenario:

    Both client and server dbs are empty.

    On the Client, I add a row to the "orders" table.

    I then run the app and synchronize the client with the server.  The server receives the changes and its "orders" table is updated with the row from the client.

    On the Server, I change the "order_date" (in Enterprise Manager).

    I then synchronize the client with the server.  Note:  I have a message box display statistics about the sync.

    The app notifies me (via the messagebox) that one row was downloaded.  Which is good.  But the client "orders" row is not updated.

     

    Problem:

    The client "orders" row is not updated.

    The client's "sp_orders_applyupdates" proc is called to perform the update.  Here it is:

     

    ALTER procedure [dbo].[sp_orders_applyupdate]

    @sync_last_received_anchor binary(8),

    @sync_row_count int out,

    @order_id int,

    @order_date datetime

    as

      update [orders]

        set [order_date] = @order_date

      where (update_timestamp <= @sync_last_received_anchor

              or update_originator_id <> 0)

            and [order_id] = @order_id

      --where [order_id] = @order_id

     

      if @@rowcount = 1

        --once you apply update from server, set your originator id to be servers

        update [orders] set update_originator_id = 1 where [order_id] = @order_id

     

      set @sync_row_count = @@rowcount

     

    The problem (at least I think it is) is that the "@sync_last_received_anchor" is the anchor value that the server uses - not the client's.  This value could be larger or smaller than the "update_timestamp" value; therefore, may or may not update based on this.  The value being sent into this proc (in the "@sync_last_received_anchor" parameter) is in fact smaller than the "update_timestamp" column.

     

    When the proc above executes, here's the values that were used during my last test:

    update_timestamp = 0x0000000000004E22

    @sync_last_received_anchor = 0x000000000000054A

     

    update_timestamp is not <= @sync_last_received_anchor

     

    The "anchor" table contains the correct values for the client and server.

     

    Questions:

    1. Shouldn't the client's "sentanchor" (from the "anchor" table for the table being processed, i.e., orders) be sent to this proc as the "@sync_last_received_anchor" and not the server's anchor value?

     

    2. I'm using the timestamp data type for the "update_timestamp" columns in the "orders" and "order_details" tables.  Should I not use the timestamp?

     

    3. Both the client and server syncProviders SelectNewAnchorCommand are set to @@DBTS.

    Is this correct?

    Here's that code:

    SqlCommand anchorCmd = new SqlCommand();

    anchorCmd.CommandType = CommandType.Text;

    anchorCmd.CommandText = "Select @" + SyncSession.SyncNewReceivedAnchor + " = @@DBTS";

    anchorCmd.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp).Direction = ParameterDirection.Output;

    serverSyncProvider.SelectNewAnchorCommand = anchorCmd;

    clientSyncProvider.SelectNewAnchorCommand = anchorCmd;

     

     

    I would greatly appreciate any help/recommendations that you can provide.  I have been struggling with this for some time now and am at the end of my rope.

      

    Thank you,

    Mark

     

    • Moved by Hengzhe Li Friday, April 22, 2011 7:43 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Monday, November 24, 2008 4:17 PM

All replies

  • I downloaded the "SQL Express Synchronization Sample" and I have found that the same thing happens to the sample.

     

    1) With the "Show Client Database Records" option selected - I can update an "orders" row and synchronize.

     

    2) Select the "Show Server Database Records" option and the change is there.  Next, I can change a row on the server and sychronize.  Select the "client database" option and the change is not there.

     

    When I look at sql profiler for my local Sql Express database, I see that the "@sync_last_received_anchor" is the server's value.  Here's what profiler spits out:

     

    exec sp_orders_applyupdate

        @order_id=1682,

        @order_date='Nov 25 2008 3:46:50:127PM,

        @sync_last_received_anchor=0x0000000000000821,

        @sync_row_count=@p4 output

     

    I checked and the @sync_last_received_anchor=0x0000000000000821 is the ReceivedAnchor value in the "anchor" table - which is the servers.  I look at the "ProviderSample_Remote" database's @@DBTS and this is the value.  The clients is 0x0000000000000FDB.

     

    Correct me if I'm wrong, but using the servers last sent anchor (in stored procedures on the client) will never work.

    Tuesday, November 25, 2008 10:11 PM

  • The SqlExpressClientSyncProvider only works when the SyncDirection is set to BiDirectional. See this post on the SQL Express Client Sync Sample.

    So, if the SyncDirection is set to anything other than BiDirectional, then this is most likely the problem.

    • Proposed as answer by Hengzhe Li Friday, April 22, 2011 7:43 AM
    Thursday, December 11, 2008 2:14 PM