locked
[SqlExpress provider] Reset Anchors to Cancel all pending Sync RRS feed

  • Question

  • I'm in a process of converting my existing application to use Sync Service for ADO.Net with SQLExpress providers. I already have fairly large tables, synchronized with DTS, I've added decoupled tracking to them and it works fine with test cases. I dont want to sync the whole data between Server and Client when I first plug them in with Sync framework, but sync should work fine thereafter.

     

    Now the question is how can I reset SentAnchor and ReceivedAnchor in anchor table so that it essentialy cancel all the changes due for Syncing? Do I require to serialize num_active_rowversion() to byte[] and save it these fields or what? Can someone explain me this concept?

    • Moved by Hengzhe Li Friday, April 22, 2011 7:49 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Wednesday, September 24, 2008 9:36 PM

Answers

  • This is what I did to reset anchors manually so that syncframework can skip changes which might have synchronized manually with DTS etc. This could be useful when you have existing data already synced between server and client and you are converting that application to use sync framework. By resetting anchors sync framework will only fetch new records / new changes.

     

    Code Snippet

    public void ResetAnchors()

    {

    SyncAnchor ReceivedAnchor = null;

    SyncAnchor SentAnchor = null;

    SqlCommand cmdNewAnchor = null;

    try

    {

    cmdNewAnchor = new SqlCommand();

    cmdNewAnchor.CommandType = CommandType.Text;

    cmdNewAnchor.CommandText = "Select @new_anchor = min_active_rowversion() - 1"; // for SQL Server 2005 earlier than SP2, use "@@DBTS + 1"

    cmdNewAnchor.Parameters.Add("@new_anchor", SqlDbType.Timestamp).Direction = ParameterDirection.Output;

     

    SqlConnection connClient = new SqlConnection(clientConnectionString);

    SqlConnection connServer = new SqlConnection(serverConectionString);

     

    connClient.Open();

    connServer.Open();

     

    using (connServer)

    {

    // Get server timestamp

    cmdNewAnchor.Connection = connServer;

    using (System.IO.MemoryStream stream = new System.IO.MemoryStream())

    {

    cmdNewAnchor.ExecuteNonQuery();

    new BinaryFormatter().Serialize(stream, (byte[])(cmdNewAnchor.Parameters["@new_anchor"].Value));

     

    ReceivedAnchor = new SyncAnchor(stream.ToArray());

    }

    }

     

    using (connClient)

    {

    // Get client timestamp

    cmdNewAnchor.Connection = connClient;

    using (System.IO.MemoryStream stream = new System.IO.MemoryStream())

    {

    cmdNewAnchor.ExecuteScalar();

    new BinaryFormatter().Serialize(stream, (byte[])(cmdNewAnchor.Parameters["@new_anchor"].Value));

     

    SentAnchor = new SyncAnchor(stream.ToArray());

    }

     

    // Reset anchor table

    string updateQry = "UPDATE dbo.anchor" +

    " SET ReceivedAnchor = @ReceivedAnchor, SentAnchor = @SentAnchor ;";

     

    SqlCommand cmdResetAnchor = new SqlCommand(updateQry, connClient);

    cmdResetAnchor.Parameters.AddWithValue("@ReceivedAnchor", ReceivedAnchor.Anchor);

    cmdResetAnchor.Parameters.AddWithValue("@SentAnchor", SentAnchor.Anchor);

     

    try

    {

    if (cmdResetAnchor.ExecuteNonQuery() == 0)

    throw new Exception("Failed to Reset Anchors!");

    }

    finally

    {

    if (cmdResetAnchor != null)

    cmdResetAnchor.Dispose();

    }

    }

    }

    catch (Exception ex)

    {

    throw ex;

    }

    finally

    {

    if (cmdNewAnchor != null)

    cmdNewAnchor.Dispose();

    }

    }

     

     

     

    Saturday, September 27, 2008 2:27 PM

All replies

  • For sql express the anchor columns are just bigint's (i think) so you should be able to convert them to bigint and store them.

    Disclaimer: We have never tried/tested this "approach" so not sure what the outcome would be for the first sync and subsequent sync's

    Thursday, September 25, 2008 11:10 PM
    Moderator
  • Hi maheshwar,

     

    In fact for sqlexpress providers anchors aren't bigints. As per the sample I downloaded from Microsoft they are byte[2048] fields and the data is serialized and saved to anchor fields. I still dont know the exact mechanism of the way sync providers are working with anchor fields. I would like to know how to reset these anchors those set by Sync framework to different values so that server and client wont synchronize until there is something new happens with data. This could be useful in my scenario since I'm converting my existing application to use sync, which already got data synced manually. So if I enable syncing these synced records will be again downloaded, I want to avoid this situation.

     

    Anyone?

     

    Friday, September 26, 2008 4:46 PM
  • This is what I did to reset anchors manually so that syncframework can skip changes which might have synchronized manually with DTS etc. This could be useful when you have existing data already synced between server and client and you are converting that application to use sync framework. By resetting anchors sync framework will only fetch new records / new changes.

     

    Code Snippet

    public void ResetAnchors()

    {

    SyncAnchor ReceivedAnchor = null;

    SyncAnchor SentAnchor = null;

    SqlCommand cmdNewAnchor = null;

    try

    {

    cmdNewAnchor = new SqlCommand();

    cmdNewAnchor.CommandType = CommandType.Text;

    cmdNewAnchor.CommandText = "Select @new_anchor = min_active_rowversion() - 1"; // for SQL Server 2005 earlier than SP2, use "@@DBTS + 1"

    cmdNewAnchor.Parameters.Add("@new_anchor", SqlDbType.Timestamp).Direction = ParameterDirection.Output;

     

    SqlConnection connClient = new SqlConnection(clientConnectionString);

    SqlConnection connServer = new SqlConnection(serverConectionString);

     

    connClient.Open();

    connServer.Open();

     

    using (connServer)

    {

    // Get server timestamp

    cmdNewAnchor.Connection = connServer;

    using (System.IO.MemoryStream stream = new System.IO.MemoryStream())

    {

    cmdNewAnchor.ExecuteNonQuery();

    new BinaryFormatter().Serialize(stream, (byte[])(cmdNewAnchor.Parameters["@new_anchor"].Value));

     

    ReceivedAnchor = new SyncAnchor(stream.ToArray());

    }

    }

     

    using (connClient)

    {

    // Get client timestamp

    cmdNewAnchor.Connection = connClient;

    using (System.IO.MemoryStream stream = new System.IO.MemoryStream())

    {

    cmdNewAnchor.ExecuteScalar();

    new BinaryFormatter().Serialize(stream, (byte[])(cmdNewAnchor.Parameters["@new_anchor"].Value));

     

    SentAnchor = new SyncAnchor(stream.ToArray());

    }

     

    // Reset anchor table

    string updateQry = "UPDATE dbo.anchor" +

    " SET ReceivedAnchor = @ReceivedAnchor, SentAnchor = @SentAnchor ;";

     

    SqlCommand cmdResetAnchor = new SqlCommand(updateQry, connClient);

    cmdResetAnchor.Parameters.AddWithValue("@ReceivedAnchor", ReceivedAnchor.Anchor);

    cmdResetAnchor.Parameters.AddWithValue("@SentAnchor", SentAnchor.Anchor);

     

    try

    {

    if (cmdResetAnchor.ExecuteNonQuery() == 0)

    throw new Exception("Failed to Reset Anchors!");

    }

    finally

    {

    if (cmdResetAnchor != null)

    cmdResetAnchor.Dispose();

    }

    }

    }

    catch (Exception ex)

    {

    throw ex;

    }

    finally

    {

    if (cmdNewAnchor != null)

    cmdNewAnchor.Dispose();

    }

    }

     

     

     

    Saturday, September 27, 2008 2:27 PM
  • HI Babu,

     

    We are using SQL Express provider for ado.net sync to develop an application to sync between SQL Server 2005(server) and SQL Express 2005(client).

     

    The scenario is as below:

     

    1. We synchronize data from server to client (which loads a fresh set of data successfuly on client)

    2. Now we change some rows on the client DB (updates) on the rows which were synchronized from the server DB.

    3. Now when we try to synchronize the changes from the client Db to Server back, it gives us a primary key violation error as below:

     

    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 anyone please tell us what could be wrong in this sql express adotpion, the same works when we use a SQL Ce on the client.

     

    The original sample which we took from the link below also has this same issue:

     

    http://code.msdn.microsoft.com/Release/ProjectReleases.aspx?ProjectName=sync&ReleaseId=1200

     

    Pls any help would be greatly appreciated



    Monday, November 10, 2008 5:18 AM
  •  

    Your Foreign Key Constraints on the server or client are prevent the data from being inserted. It all depends on the order the tables were added to the syncgroup and also the order the syncgroups where added to the sync provider.

     

    The best thing is to create syncgroups so sync group 1 has all of the seed tables data,

     

    SyncGroup 2 has the parent Tables

     

    SyncGroup 3 has the child tables that have the parent table as references.

     

    That wat you can ensure that the data is added to the database in the correct order.

     

     

    What seems to be missing is if there is an error durring a sync the anchor timestamp get updated regardless so if the error is corrected on the server the client never trys to download the changes unless the record timestamp changes.

     

    Saturday, November 22, 2008 9:36 PM