none
Custom Change tracking: MS SQL server 2005 and Codeplex code RRS feed

  • Question

  • Hi,

    I am trying to do a Sync operation between SQL CE compact database(.SDF)  and MS SQL Server 2005 database. I used Codeplex code to test the scenario. I set up everything based on the steps mentioned in the example.

    SQL CE  3.5 sp1

    MS Visual Studio 2008

    MS .NET Framework 3.5

    MS .NET Compact Framework 3.5

     

    When i tried the sync, geting the following error.

    table name 'Customer' is not valid. This might be caused by one or more of the following issues:  unable to find a SyncAdapter for the specified SyncTable; the table name is null or empty; or the table name contains the keywords SET, FMTONLY, and OFF.

    Please let me know, how to solve this issue.

     

    Thanks in Advance,

    Jagadeesh

    Tuesday, December 7, 2010 5:47 AM

Answers

All replies

  • it would help if you can post at least the code snippet or the actual link to the codeplex project you're referring to.
    Tuesday, December 7, 2010 3:53 PM
    Moderator
  • Server database creation script:

    http://msdn.microsoft.com/en-us/library/cc305973.aspx

     

    Server Code :

     AddSyncAdapter("Sales.Customer", SyncDirection.Bidirectional);

    protected override SyncAdapter BidirectionalSyncAdpaterHelper(string table)
            {
                var builder = new SqlSyncAdapterBuilder
                                  {
                                      ChangeTrackingType = ChangeTrackingType.CoupledChangeTracking,
                                      Connection = Connection as SqlConnection,
                                      SyncDirection = SyncDirection.Bidirectional,
                                      TableName = table,
                                      CreationTrackingColumn = "InsertTimestamp",
                                      CreationOriginatorIdColumn = "InsertId",
                                      UpdateTrackingColumn = "UpdateTimestamp",
                                      UpdateOriginatorIdColumn = "UpdateId",
                                      DeletionTrackingColumn = "DeleteTimestamp",
                                      DeletionOriginatorIdColumn = "DeleteId",
                                      TombstoneTableName = table + "_Tombstone"
                                  };

                return builder.ToSyncAdapter();
            }

     var selectNewAnchorCommand = new SqlCommand
                {
                    CommandText = "usp_GetNewBatchAnchor",
                    CommandType = CommandType.StoredProcedure
                };
                selectNewAnchorCommand.Parameters.Add("@" +
                  SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp, 8);
                selectNewAnchorCommand.Parameters.Add("@" +
                  SyncSession.SyncMaxReceivedAnchor, SqlDbType.Timestamp, 8);
                selectNewAnchorCommand.Parameters.Add("@" +
                  SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp, 8);
                selectNewAnchorCommand.Parameters.Add("@" +
                  SyncSession.SyncBatchSize, SqlDbType.Int, 4);
                selectNewAnchorCommand.Parameters.Add("@" +
                  SyncSession.SyncBatchCount, SqlDbType.Int, 4);
                selectNewAnchorCommand.Parameters["@" +
                  SyncSession.SyncMaxReceivedAnchor].Direction =
                    ParameterDirection.Output;
                selectNewAnchorCommand.Parameters["@" +
                  SyncSession.SyncNewReceivedAnchor].Direction =
                    ParameterDirection.Output;
                selectNewAnchorCommand.Parameters["@" +
                  SyncSession.SyncBatchCount].Direction =
                    ParameterDirection.InputOutput;
                // Set to the DbServerSyncProvider
                this.SelectNewAnchorCommand = selectNewAnchorCommand;
                // Set the requested batch size
                this.BatchSize = 100;

     

    Here this is DBServerSyncProvider object.

     

    Client Code:

    ClientSyncAgent : SyncAgent

     

    Inside ClientSyncAgent:

    SyncGroup uberGroup = new SyncGroup("UberGroup");

     this.Configuration.SyncTables.Add(SyncUtility.GetDownloadOnlySyncTable("Customer", uberGroup));

     

    SqlServer profiler statements:

     

     SET FMTONLY OFF; SET NO_BROWSETABLE ON; SET FMTONLY ON;SELECT * FROM Sales.[Customer] SET FMTONLY OFF; SET NO_BROWSETABLE OFF;

     SET FMTONLY OFF; SET NO_BROWSETABLE ON; SET FMTONLY ON;SELECT * FROM Sales.[Customer] SET FMTONLY OFF; SET NO_BROWSETABLE OFF;

    exec sp_reset_connection

    -- network protocol: LPC
    set quoted_identifier on
    set arithabort off
    set numeric_roundabort off
    set ansi_warnings on
    set ansi_padding on
    set ansi_nulls on
    set concat_null_yields_null on
    set cursor_close_on_commit off
    set implicit_transactions off
    set language us_english
    set dateformat mdy
    set datefirst 7
    set transaction isolation level read committed

     SET FMTONLY OFF; SET NO_BROWSETABLE ON; SET FMTONLY ON;SELECT * FROM Sales.[Customer_Tombstone] SET FMTONLY OFF; SET NO_BROWSETABLE OFF;

     SET FMTONLY OFF; SET NO_BROWSETABLE ON; SET FMTONLY ON;SELECT * FROM Sales.[Customer_Tombstone] SET FMTONLY OFF; SET NO_BROWSETABLE OFF;

    exec sp_reset_connection

    -- network protocol: LPC
    set quoted_identifier on
    set arithabort off
    set numeric_roundabort off
    set ansi_warnings on
    set ansi_padding on
    set ansi_nulls on
    set concat_null_yields_null on
    set cursor_close_on_commit off
    set implicit_transactions off
    set language us_english
    set dateformat mdy
    set datefirst 7
    set transaction isolation level read committed


    declare @p2 binary(8)
    set @p2=0x0000000000000FA0
    declare @p3 binary(8)
    set @p3=0x0000000000000835
    declare @p5 int
    set @p5=20
    exec usp_GetNewBatchAnchor @sync_last_received_anchor=0x0000000000000000,@sync_max_received_anchor=@p2 output,@sync_new_received_anchor=@p3 output,@sync_batch_size=100,@sync_batch_count=@p5 output
    select @p2, @p3, @p5

    Error message in the log:

     

    table name 'Customer' is not valid. This might be caused by one or more of the following issues:  unable to find a SyncAdapter for the specified SyncTable; the table name is null or empty; or the table name contains the keywords SET, FMTONLY, and OFF.

     

    Wednesday, December 8, 2010 2:49 AM
  • have you checked all you code to make sure that you're reference Sales.Customer instead fo Customer only? 

    for example, this line

     this.Configuration.SyncTables.Add(SyncUtility.GetDownloadOnlySyncTable("Customer", uberGroup));

    is referencing "Customer". shouldnt it be "Sales.Customer"?

    Wednesday, December 8, 2010 9:43 AM
    Moderator
  • Sales is the schema name and which is not applicable for mobile database. That is why i didn't specify that in the client code and simply specified the table name, which is Customer.

    Wednesday, December 8, 2010 10:43 AM
  • can you post the code for AddSyncAdapter?

    or you may try following the steps in the documentation: How to: Create Views of Data on a Client

    Wednesday, December 8, 2010 2:54 PM
    Moderator
  •  AddSyncAdapter("Sales.Customer", SyncDirection.Bidirectional);

     

    protected void AddSyncAdapter(string table,SyncDirection syncDirection)
            {
                //by default, assume bidirectional mode
                Func<string,SyncAdapter> helper = BidirectionalSyncAdpaterHelper;


                switch (syncDirection)
                {
                    case (SyncDirection.Snapshot):
                        helper = SnapshotSyncAdpaterHelper;
                        break;
                    case (SyncDirection.DownloadOnly):
                        helper = DownloadOnlySyncAdpaterHelper;
                        break;
                    case (SyncDirection.UploadOnly):
                        helper = UploadOnlySyncAdpaterHelper;
                        break;
                }

                SyncAdapters.Add(helper(table));

            }

    protected override SyncAdapter BidirectionalSyncAdpaterHelper(string table)
            {
                var builder = new SqlSyncAdapterBuilder
                                  {
                                      ChangeTrackingType = ChangeTrackingType.CoupledChangeTracking,
                                      Connection = Connection as SqlConnection,
                                      SyncDirection = SyncDirection.Bidirectional,
                                      TableName = table,
                                      CreationTrackingColumn = "InsertTimestamp",
                                      CreationOriginatorIdColumn = "InsertId",
                                      UpdateTrackingColumn = "UpdateTimestamp",
                                      UpdateOriginatorIdColumn = "UpdateId",
                                      DeletionTrackingColumn = "DeleteTimestamp",
                                      DeletionOriginatorIdColumn = "DeleteId",
                                      TombstoneTableName = table + "_Tombstone"
                                  };

                return builder.ToSyncAdapter();
            }

     

    check AddSyncAdapter method. From AdSyncAdapter, work will be delegated Bidirectional menthod...

    Wednesday, December 8, 2010 3:22 PM
  • problem solved.

    Mistake in my AddSyncAdapter code. I recitified it.

    Thanks for the help.

    • Marked as answer by Jagadeesh Babu Monday, December 20, 2010 5:52 AM
    Friday, December 10, 2010 3:04 AM