locked
What is the best anchor method for a SQL 2008 vs. SQL CE 3.5 scenario over WCF? RRS feed

  • Question

  • What is the best anchor method for a SQL 2008 vs. SQL CE 3.5 scenario over WCF? I am looking for guidance, sample code, or a reference to documentation that will help me understand how to implement row filtering. I started out with this sample code http://msdn.microsoft.com/en-us/library/bb726003.aspx, but this seemed to be geared towards SQL 2005.

                //Create a connection to the server database.
                string connectionString = global::DoveTail.TMOTSyncService.Properties.Settings.Default.ServerDoveTail2ConnectionString;
                SqlConnection serverConn = new SqlConnection(connectionString);
                this.Connection = serverConn;
    
                SqlCommand selectNewAnchorCommand = new SqlCommand();
                string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
                selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1";
                selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);
                selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
                selectNewAnchorCommand.Connection = serverConn;
                this.SelectNewAnchorCommand = selectNewAnchorCommand;
    
                //Create a filter parameter that will be used in the filter clause for
                //all tables.
                SqlParameter filterParameter = new SqlParameter("@User_Id", SqlDbType.NVarChar);
    
                //Person table.
                SqlSyncAdapterBuilder personBuilder = new SqlSyncAdapterBuilder(serverConn);
    
                personBuilder.TableName = "Person";
                personBuilder.SyncDirection = SyncDirection.Bidirectional;
                personBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;
    
                string personFilterClause =
                    "Person.id IN (SELECT Person_id FROM dbo.Member m " +
                                    "WHERE m.Id IN " +
                                        "(SELECT Member_id FROM dbo.UserMemberDownloaded " +
                                            "WHERE User_id=@User_Id))";
                personBuilder.FilterClause = personFilterClause;
                personBuilder.FilterParameters.Add(filterParameter);
    
                SyncAdapter personSyncAdapter = personBuilder.ToSyncAdapter();
                personSyncAdapter.TableName = "Person";
                this.SyncAdapters.Add(personSyncAdapter);
    

     

    Above is a code snippet from my server sync provider that throws an exception:

    System.InvalidCastException, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 
    Failed to convert parameter value from a Byte[] to a Int64.

    I obviously mixed two different concepts. I was hoping that the sync framework would handle the anchor automatically, but that does not appear to be the case. When I remove the anchor command, the exception goes away and the server query sees the following parameters:

    @sync_last_received_anchor=0
    @sync_new_received_anchor=0

    and consequently, nothing gets sync'd. I need to understand this mechanism, but can't find the resources that would help me with this.

    • Edited by cdonner Wednesday, June 10, 2009 6:40 PM
    • Moved by Hengzhe Li Friday, April 22, 2011 2:59 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Wednesday, June 10, 2009 6:39 PM

Answers

  •             selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = change_tracking_current_version()";
                selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.BigInt);
    


    found the answer here: http://msdn.microsoft.com/en-us/library/cc305322.aspx.

    This is still not working, but I will ask a new question.
    • Marked as answer by cdonner Thursday, June 11, 2009 1:27 AM
    Wednesday, June 10, 2009 7:25 PM

All replies

  • Here is the query that executes on the server and is supposed to get the new anchor:

    exec sp_executesql N'SELECT * FROM sys.change_tracking_tables WHERE object_id = object_id(@sync_table_name)',N'@sync_table_name nvarchar(6)',@sync_table_name=N'Person'		

    Here is what it returns:

    object_id	is_track_columns_updated_on	min_valid_version	begin_version	cleanup_version
    278292051	0	0	0	0
    And yet:
    select * from CHANGETABLE(CHANGES [Person], 0) CT 

    and the results

    SYS_CHANGE_VERSION	SYS_CHANGE_CREATION_VERSION	SYS_CHANGE_OPERATION	SYS_CHANGE_COLUMNS	SYS_CHANGE_CONTEXT	id
    623	623	I	NULL	NULL	BA96BD1C-B943-4AC2-B3C0-319033103E25
    Help!
    Wednesday, June 10, 2009 6:55 PM
  •             selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = change_tracking_current_version()";
                selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.BigInt);
    


    found the answer here: http://msdn.microsoft.com/en-us/library/cc305322.aspx.

    This is still not working, but I will ask a new question.
    • Marked as answer by cdonner Thursday, June 11, 2009 1:27 AM
    Wednesday, June 10, 2009 7:25 PM