locked
Problem in SQLExpressProviderSample for ADO.Net Sync RRS feed

  • Question

  •  

    Hi,

     

    We have tested the sample provided by Microsoft using SQLExpressProvider for the Sync process between SQL Express and SQL Express.

     

    We are having VS 2008, SQL Express 2005, Sync Framework.

     

    We tried the sample and we are facing the following issue

     

    1. Inserting a new row in Server

    2. Synchronizing between server and client

    3. Inserted row is added to client

    4. Updating the same row in the client

    5. Synching back to the server

    6. Update is not reflected in the server, instead we are getting a conflict message of duplicate row inserting.

     

     

    why this is happening?, if any body has any clue...

     

    Jawahar
    • Moved by Max Wang_1983 Thursday, April 21, 2011 6:00 PM forum consolidation (From:SyncFx - Technical Discussion [ReadOnly])
    Thursday, November 6, 2008 7:24 AM

Answers

  • Are you using identity columns in your database on the table you try to update? Because SQL server compact does not support identity colums, it does not recognize the ID colums as identity so it cannot insert a new value. You should use uniqueidentifiers for all your identity colums.

     

    Tuesday, November 11, 2008 8:03 AM

All replies

  • When you do UPDATE on #4,  which row was updated, from INSERT on #1 or INSERT on #3?

    But one thing you may want to check is the sync adapter object you created and added to the express client provider of this table in synchronization, the select incremental insert command and select incremental update command.

    I suspect you got INSERT enumerated instead of the UPDATE so you see a conflict in the upload phase on the SERVER side.

     

    Thanks.

    Thursday, November 6, 2008 7:39 AM
    Answerer
  • HI  L Zhou ,

     

      IN the Line #3 we are not inserting a new row in the client, but after the sync, the row which was created on the server is getting dowloaded to the client, but once it is downloaded we are updating a value in the row from client side, then again we are syncing back. Now the changes are not getting updated to the server, instead we are getting the conflict message as below

     

     

    Conflict while applying changes to the server.

     

    Cannot insert duplicate key

     

    and we are having the command as below for incrinsert and incrupdate

     

    // incr insert com

    SqlCommand incrInsCmd = new SqlCommand();

    incrInsCmd.CommandType = CommandType.StoredProcedure;

    incrInsCmd.CommandText = "sp_orders_incrinserts";

    incrInsCmd.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Binary, 8);

    incrInsCmd.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Binary, 8);

    adapterOrders.SelectIncrementalInsertsCommand = incrInsCmd;

    // incr update com

    SqlCommand incrUpdateCmd = incrInsCmd.Clone();

    incrUpdateCmd.CommandText = "sp_orders_incrupdates";

    adapterOrders.SelectIncrementalUpdatesCommand = incrUpdateCmd;

     

     

    please give me your suggestions, whether i am doing anything wrong, and again we have downloaded this sample and we havent modified anything.

     

    Jawahar

    Thursday, November 6, 2008 8:41 AM
  • Thanks for help clearifying the data modification steps.

    I assume the "sp_orders_incrinserts" and "sp_orders_incrupdates" were same/similar as listed below.

    They are used to enumerate changes from the express/client database right?  (not one on the server).

    If this is TRUE, then I think you should modify your command using parameter

    (SqlExpressSyncSession.SyncExpressLastSentAnchor, SqlDbType.Binary, 8) which represents the watermark on the express/client to have sent all changes to the server.

     

    SyncSession.SyncLastReceivedAnchor and SyncSession.SyncNewReceivedAnchor are anchor values from the Server Side which should be irrelevant to enumerate the local changes.

     

    Also you may want to check how many tracking columns do you have in your [orders] table on the express/client database.  If you like to track both local update and local create, you need to have 2 fields, such as create_timestamp and update_timestamp.

     

    Please turn on SQL Profiler upon the express/client to captuer which TQL was executed and if any rows coming back so that you can do the same query later for analysis.

     

    Thanks.

     

    CREATE PROCEDURE dbo.sp_orders_incrinserts (
     @sync_express_last_sent_anchor binary(8) )
    as
     SELECT o.order_id, o.order_date
     FROM orders o
     WHERE o.create_timestamp > @sync_express_last_sent_anchor and o.update_originator_id = 0
     ORDER BY o.create_timestamp
    go

    CREATE PROCEDURE dbo.sp_orders_incrupdates (
     @sync_express_last_sent_anchor binary(8))
    as
     SELECT o.order_id, o.order_date
     FROM orders o
     WHERE o.create_timestamp <= @sync_express_last_sent_anchor and
        o.update_timestamp > @sync_express_last_sent_anchor
        and o.update_originator_id = 0
     ORDER BY o.update_timestamp
    go

    Friday, November 7, 2008 12:24 AM
    Answerer
  • Hi,

    ""I assume the "sp_orders_incrinserts" and "sp_orders_incrupdates" were same/similar as listed below.""

    -----NO ---

    Following are the stored procedures which are copied from my ProviderSample_Local DB runned from the sqlExpressproviderSample provided in the Microsoft website for sync sample.

    ALTER PROCEDURE [dbo].[sp_orders_incrinserts] (
        @sync_last_received_anchor binary(8),
        @sync_new_received_anchor binary(8) )
    as
        SELECT o.order_id, o.order_date
        FROM orders o
        WHERE o.create_timestamp between @sync_last_received_anchor + 1 and @sync_new_received_anchor and o.update_originator_id = 0
        ORDER BY o.create_timestamp
        -- the timestamps on the select increment s-procs should really be last_sent_anchor and new_sent_anchor, but named like this to
       -- make work with DBServerSyncProvider

    ALTER PROCEDURE [dbo].[sp_orders_incrupdates] (
       @sync_last_received_anchor binary(8),
        @sync_new_received_anchor binary(8) )
    as
       SELECT o.order_id, o.order_date
        FROM orders o
       WHERE o.create_timestamp <= @sync_last_received_anchor and
                  o.update_timestamp between @sync_last_received_anchor + 1 and @sync_new_received_anchor
                 and o.update_originator_id = 0
        ORDER BY o.update_timestamp

    "Also you may want to check how many tracking columns do you have in your [orders] table on the express/client database.  If you like to track both local update and local create, you need to have 2 fields, such as create_timestamp and update_timestamp."

    I have checked in my Client and Server DB for the above said fields and i have all the columns.

    Tried by changing the sps as you said and tried to execute the same but still throws the same error

    Error Message:

     

    Conflict detected while applying changes to the server.

     

    Table: Orders.

    Conflict Type: ClientInsertServerInsert.

    Error: Violation of PRIMARY KEY Constraint ‘PK_orders_7c848OAE’. Cannot insert duplicate key in key in object ‘dbo.orders’.

    The statement has been terminated.

    Order_id:381.

    The server row will not be updated.


    Could you please send me a working copy of "sqlExpressproviderSample"
    to my mail id(jawahar@excelenciaconsulting.com).


    Thanks in advance....!!!!!!







    Friday, November 7, 2008 7:13 AM
  • Are you using identity columns in your database on the table you try to update? Because SQL server compact does not support identity colums, it does not recognize the ID colums as identity so it cannot insert a new value. You should use uniqueidentifiers for all your identity colums.

     

    Tuesday, November 11, 2008 8:03 AM
  • Hi Rick,

    Thanks for your reply... we are not using SQL Server Compact. We Use SQL2005 Express for server and client synch.

    Thanks in advance.....
    Tuesday, November 11, 2008 10:16 AM
  •  

    In response to your reply on my post.  We created our own sync procedure within VB.Net using the sample as a guide.  We scrapped using the sample and never figured out why it was not working.
    Tuesday, November 18, 2008 10:13 PM
  • Hi Trey,

     

    Could you please provide us the sync procedure which you have developed, probably we could make use of the same.

     

    It would be of great help, as this sample provider is not working well for us.

    Monday, December 15, 2008 9:17 AM
  • Hi Rick,

     

    In contuniuation for your earlier question, yes we are using uniqueidentifies as GUID's for our identity columns.

     

    Could you please let us know any work arounds to get past this issue in using sql express for sync

    Monday, December 15, 2008 9:18 AM