locked
Client and Server Not Synching due to @@DBTS RRS feed

  • Question

  • Hi,

     

    I am working with the SqlExpressClientSyncProvider to synch my client-side Sql Express 2005 db with my server-side Sql Server 2000 db.  I'm using Sql Server 2000 because that's what my client has.  I'm developing in VS 2005.  I using the SqlExpressProviderSample sample to get familiar with and play around with synch services before I implement in my actual solution.

     

    The problem I'm having is - the @@DBTS value in my client express database is larger than the @@DBTS value on my server database.  So, I'm not able to synch due to this.

     

    So, if my client "anchor" table's "sentanchor" and "receivedanchor" columns are null and I create a row in my orders table, then the create_timestamp (a bigint) will be set to @@DBTS+1.  If I synch, everything's good.  If I change the "order_date" column on the server and synch, the syncstatus tells me 1 row was downloaded...but the row is not updated (in the client database) when the "sp_orders_applyupdate" is executed.  This is because either the create_timestamp (a bigint) and/or the update_timestamp (a timestamp) column values are greater than the @@DBTS value that was generated on the server when the change was made.

    If I change the "sp_orders_applyupdate" client stored procedure's where clause - 

    FROM:

    where (update_timestamp <= @sync_last_received_anchor or update_originator_id <> 0) and [order_id] = @order_id

    TO:

    where [order_id] = @order_id

     

    Then all is good and the client row is updated.  So I know the differences in timestamps (between the client and server) are the problem.

     

    I'm determining the value of @@DBTS (on the client and server) by doing the following:  CAST(@@DBTS AS BIGINT)

    Is CASTing this the correct way to determine the value of @@DBTS?  If not, how can I determine the true value?

     

    I know I'm missing something here because it seems that @@DBTS is the proposed usage...but I just don't see how this will work because if the client value can become larger than the servers.  What do I need to do in order to correct this?

     

    Someone please straighten me out here.  Smile

     

    Thanks for your help,

    Mark

    • Moved by Hengzhe Li Friday, April 22, 2011 7:50 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Friday, November 21, 2008 6:16 PM

All replies

  • I'm not building a custom provider, but I can tell you that I found keeping track of changes on the server much easier when I switched them to use dates instead of binary\int (dbts) values.  You'd need to use UTC dates, but I think that may work better for you if it is possible to change to dates on the client and the server.

     

    This is where my limited knowledge of client providers may get in the way, but I'm not clear on why you are comparing the client change stamp to the server change stamp when you download rows from the server.  Shouldn't you compare the row's change stamp to the "last sent anchor"?  If the row's stamp is greater than the lastsentanchor, then that means that a change you have made was never sent to the server.  At which point you have a potential conflict.

     

    Just some thoughts...

    Friday, November 21, 2008 8:54 PM
  • Thanks Daniel.  See my comments below in blue

     

     

    I'm not building a custom provider, but I can tell you that I found keeping track of changes on the server much easier when I switched them to use dates instead of binary\int (dbts) values.  You'd need to use UTC dates, but I think that may work better for you if it is possible to change to dates on the client and the server.

    I thought about going the date route - but it seems that the standard is using the @@DBTS.  Are you basically using columns like "create_datetime" and "update_datetime" on the client and server?  And then setting the "update_datetime" in your trigger?

     

    This is where my limited knowledge of client providers may get in the way, but I'm not clear on why you are comparing the client change stamp to the server change stamp when you download rows from the server.  Shouldn't you compare the row's change stamp to the "last sent anchor"?  If the row's stamp is greater than the lastsentanchor, then that means that a change you have made was never sent to the server.  At which point you have a potential conflict.

    I'm comparing the servers "last sent anchor" on the client when the sp_orders_applyupdate is executed.  This is passed into it.  The problem is that the "create_timestamp" (on the client) is always greater than even the newest anchor (and the last sent anchor) on the server.  The "update_timestamp" on the client is the same.

     

    Are you saying that you think my where clause should be: 

    (a) where (update_timestamp <= @sync_last_received_anchor or update_originator_id <> 0) and [order_id] = @order_id

    OR

    (b) where [order_id] = @order_id

     

     

    Just some thoughts...

    Friday, November 21, 2008 9:45 PM
  • For the first one, yes I am using datetime as you specify.  I impletmented datetime because it was much easier to troubleshoot and because I am syncing across multiple databases on the server in some cases and that was the only way I could think of to guarantee accuracy.  I think I was looking through the generated code in the sync designer when I discovered an example of using datetime.  Maybe it was an sample I saw somewhere.  Don't remember for sure.  It ended up being pretty easy to do though.

     

    For the second one - If @sync_last_received_anchor is the server anchor, I don't think it applies at all to your query.  You keep track of what has been sent to the server on the client only.  When you download data to the client, all the client row needs to know is how it's timestamp values compare to the "sent" counter.  So the answer is closer to (b), but you probably will need to check that the row's values are >= the last sent counter in order to verify against conflicts.  However, I'm not sure what you need to do to keep rows that were brought down from the server from being sent right back up the next time you send data though, since those changes will cause counter updates on the row.  This is probably another part of the issue you are dealing with.

     

    Hope that helps at least a little bit...

    Friday, November 21, 2008 10:25 PM
  • Thanks Daniel.  I appreciate it.

     

    I'm going to look at using the database timestamp a little more.  I'll probably end up going with the datetime as you did.  The troubleshooting aspect alone is huge.

     

    -Mark

     

    • Unmarked as answer by Tina_Tian Friday, April 22, 2011 7:50 AM
    Friday, November 21, 2008 10:51 PM