The _selectchanges stored procedures is always called with sync_min_timestamp= 9223372036854775807

• Question

• 

  

  0

  Sign in to vote

  Hi,

  I am using SyncFX2.1 for syncronization in my application which uses SQL Express on client and SqlServer database on server side.My question is that even if I call sync again and again, the selectchanges store proc on server is run with sync_min_timestamp as 9223372036854775807.the following script is the trace record from sql sever profiler. and there isn't any error  message from the DBApplyChangeFailedEventArgs by handling the provider's ApplyChangeFailed event  or log file.

  exec [BusinessKey_selectchanges] @sync_min_timestamp=9223372036854775807,@sync_scope_local_id=1,@sync_scope_restore_count=0,@sync_update_peer_key=0,@UserId='F8759128-ACFB-4FC7-B831-0768B8D4CE97',@RangeId=0

  The following code is for configuring DBSyncProvider:

          protected virtual void ConfigureProvider(FilterParams paramsInfo)

  {

             SqlConnection sqlconn = new SqlConnection(this.Connection.ConnectionString);
              sqlconn.Open();
              SqlCommand cmd = new SqlCommand(String.Format("SELECT sc.* FROM scope_config sc JOIN scope_info si ON sc.config_id=si.scope_config_id WHERE si.sync_scope_name='{0}'", ScopeName), sqlconn);
              SqlDataReader reader = cmd.ExecuteReader();
              if (reader.Read() == true)
              {
                  m_strConfigData = reader["config_data"].ToString();
              }

              //parse config data
              StringReader txtReader = new StringReader(m_strConfigData);
              XmlSerializer xmlSerializer = new XmlSerializer(typeof(SqlSyncProviderScopeConfiguration));
              SqlSyncProviderScopeConfiguration psc = (SqlSyncProviderScopeConfiguration)xmlSerializer.Deserialize(txtReader);
              txtReader.Dispose();

              foreach (SqlSyncProviderAdapterConfiguration ac in psc.AdapterConfigurations)
              {
                  DbSyncAdapter adapter = ac.BuildAdapter(new SqlConnection(this.Connection.ConnectionString));
                  //SelectIncrementalChanges
                  SqlCommand cmdSelectIncrementalChanges = new SqlCommand();
                  cmdSelectIncrementalChanges.CommandType = CommandType.StoredProcedure;
                  cmdSelectIncrementalChanges.CommandText = ac.SelectChangesProcName;
                  cmdSelectIncrementalChanges.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
                  cmdSelectIncrementalChanges.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
                  cmdSelectIncrementalChanges.Parameters.Add("@" + DbSyncSession.SyncScopeRestoreCount, SqlDbType.Int);
                  cmdSelectIncrementalChanges.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
                  // pass  the filter parameter value here
                  if (FilteredTables == null || FilteredTables.Length==0 || FilteredTables.Contains(ac.TableName))
                  {
                      foreach (var item in paramsInfo)
                      {
                          cmdSelectIncrementalChanges.Parameters.Add(new SqlParameter(item.Key, item.Value.Value));
                      }                   
                  }
                  adapter.SelectIncrementalChangesCommand = cmdSelectIncrementalChanges;

                  //InsertCommand (finished in table columns)
                  SqlCommand cmdInsert = new SqlCommand();
                  cmdInsert.CommandType = CommandType.StoredProcedure;
                  cmdInsert.CommandText = ac.InsertProcName;
                  cmdInsert.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
                  adapter.InsertCommand = cmdInsert;

                  //UpdateCommand (finished in table columns)
                  SqlCommand cmdUpdate = new SqlCommand();
                  cmdUpdate.CommandType = CommandType.StoredProcedure;
                  cmdUpdate.CommandText = ac.UpdateProcName;
                  cmdUpdate.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
                  cmdUpdate.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
                  cmdUpdate.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int);
                  adapter.UpdateCommand = cmdUpdate;

                  //DeleteCommand (finished in table columns)
                  SqlCommand cmdDelete = new SqlCommand();
                  cmdDelete.CommandType = CommandType.StoredProcedure;
                  cmdDelete.CommandText = ac.DeleteProcName;
                  cmdDelete.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
                  cmdDelete.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
                  cmdDelete.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int);
                  adapter.DeleteCommand = cmdDelete;

                  //SelectRowCommand (finished in table columns)
                  SqlCommand cmdSelectRow = new SqlCommand();
                  cmdSelectRow.CommandType = CommandType.StoredProcedure;
                  cmdSelectRow.CommandText = ac.SelectRowProcName;
                  cmdSelectRow.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
                  cmdSelectRow.Parameters.Add("@" + DbSyncSession.SyncScopeRestoreCount, SqlDbType.Int);
                  adapter.SelectRowCommand = cmdSelectRow;

                  //InsertMetadataCommand (finished in table columns)
                  SqlCommand cmdInsertMetadata = new SqlCommand();
                  cmdInsertMetadata.CommandType = CommandType.StoredProcedure;
                  cmdInsertMetadata.CommandText = ac.InsertMetadataProcName;
                  cmdInsertMetadata.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
                  cmdInsertMetadata.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
                  cmdInsertMetadata.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
                  cmdInsertMetadata.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
                  cmdInsertMetadata.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
                  cmdInsertMetadata.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
                  cmdInsertMetadata.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int);
                  cmdInsertMetadata.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
                  cmdInsertMetadata.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
                  adapter.InsertMetadataCommand = cmdInsertMetadata;

                  //UpdateMetadataCommand (finished in table columns)
                  SqlCommand cmdUpdateMetadata = new SqlCommand();
                  cmdUpdateMetadata.CommandType = CommandType.StoredProcedure;
                  cmdUpdateMetadata.CommandText = ac.UpdateMetadataProcName;
                  cmdUpdateMetadata.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
                  cmdUpdateMetadata.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
                  cmdUpdateMetadata.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
                  cmdUpdateMetadata.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
                  cmdUpdateMetadata.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
                  cmdUpdateMetadata.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
                  cmdUpdateMetadata.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int);
                  cmdUpdateMetadata.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
                  cmdUpdateMetadata.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
                  adapter.UpdateMetadataCommand = cmdUpdateMetadata;

                  //DeleteMetadataCommand (finished in table columns)
                  SqlCommand cmdDeleteMetadata = new SqlCommand();
                  cmdDeleteMetadata.CommandType = CommandType.StoredProcedure;
                  cmdDeleteMetadata.CommandText = ac.DeleteMetadataProcName;
                  cmdDeleteMetadata.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
                  cmdDeleteMetadata.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
                  cmdDeleteMetadata.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
                  adapter.DeleteMetadataCommand = cmdDeleteMetadata;

                  //table columns
                  foreach (DbSyncColumnDescription col in ac.Columns)
                  {
                      SqlDbType type = SqlDbType.Text;
                      int size = 0;
                      //type
                      switch (col.Type)
                      {
                          case "uniqueidentifier":
                              type = SqlDbType.UniqueIdentifier;
                              break;
                          case "int":
                              type = SqlDbType.Int;
                              break;
                          case "nvarchar":
                              type = SqlDbType.NVarChar;
                              break;
                          case "float":
                              type = SqlDbType.Float;
                              break;
                          case "datetime":
                              type = SqlDbType.DateTime;
                              break;
                          case "bit":
                              type = SqlDbType.Bit;
                              break;
                          case "varbinary":
                              type = SqlDbType.VarBinary;
                              break;
                          case "bigint":
                              type = SqlDbType.BigInt;
                              break;
                      }
                      //size
                      if (col.Size != "max")
                      {
                          size = Convert.ToInt32(col.Size);
                      }
                      //primary key(s)
                      if (col.IsPrimaryKey)
                      {
                          adapter.RowIdColumns.Add(col.UnquotedName);
                          //delete command
                          ((SqlCommand)adapter.DeleteCommand).Parameters.Add(col.ParameterName, type, size, col.UnquotedName);
                          //selectrow command
                          ((SqlCommand)adapter.SelectRowCommand).Parameters.Add(col.ParameterName, type, size, col.UnquotedName);
                          //insertmetadata command
                          ((SqlCommand)adapter.InsertMetadataCommand).Parameters.Add(col.ParameterName, type, size, col.UnquotedName);
                          //updatemetadata command
                          ((SqlCommand)adapter.UpdateMetadataCommand).Parameters.Add(col.ParameterName, type, size, col.UnquotedName);
                          //deletemetadata command
                          ((SqlCommand)adapter.DeleteMetadataCommand).Parameters.Add(col.ParameterName, type, size, col.UnquotedName);
                      }
                      //insert command
                      ((SqlCommand)adapter.InsertCommand).Parameters.Add(col.ParameterName, type, size, col.UnquotedName);
                      //update command
                      ((SqlCommand)adapter.UpdateCommand).Parameters.Add(col.ParameterName, type, size, col.UnquotedName);
                  }
                  this.SyncAdapters.Add(adapter);
              }
              //SelectNewTimestampCommand
              SqlCommand selectNewTimestampCommand = new SqlCommand();
              string newTimestampVariable = "@" + DbSyncSession.SyncNewTimestamp;
              selectNewTimestampCommand.CommandText = "SELECT " + newTimestampVariable + " = min_active_rowversion() - 1";
              selectNewTimestampCommand.Parameters.Add(newTimestampVariable, SqlDbType.Timestamp);
              selectNewTimestampCommand.Parameters[newTimestampVariable].Direction = ParameterDirection.Output;
              this.SelectNewTimestampCommand = selectNewTimestampCommand;

              //this.SelectNewTimestampCommand = this.get
              //SelectScopeInfoCommand
              SqlCommand selReplicaInfoCmd = new SqlCommand();
              selReplicaInfoCmd.CommandType = CommandType.Text;
              selReplicaInfoCmd.CommandText = "SELECT " +
                                               "scope_id, " +
                                               "scope_local_id, " +
                                               "scope_sync_knowledge, " +
                                               "scope_tombstone_cleanup_knowledge, " +
                                               "scope_timestamp " +
                                               "FROM scope_info " +
                                               "WHERE sync_scope_name = @" + DbSyncSession.SyncScopeName;
              selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
              this.SelectScopeInfoCommand = selReplicaInfoCmd;
             
              //UpdateScopeInfoCommand
              SqlCommand updReplicaInfoCmd = new SqlCommand();
              updReplicaInfoCmd.CommandType = CommandType.Text;
              updReplicaInfoCmd.CommandText = "UPDATE scope_info SET " +
                                               "scope_sync_knowledge = @" + DbSyncSession.SyncScopeKnowledge + ", " +
                                               "scope_id = @" + DbSyncSession.SyncScopeId + ", " +
                                               "scope_tombstone_cleanup_knowledge = @" + DbSyncSession.SyncScopeCleanupKnowledge + " " +
                                               "WHERE sync_scope_name = @" + DbSyncSession.SyncScopeName + " AND " +
                                               " ( @" + DbSyncSession.SyncCheckConcurrency + " = 0 OR scope_timestamp = @" + DbSyncSession.SyncScopeTimestamp + "); " +
                                               "SET @" + DbSyncSession.SyncRowCount + " = @@rowcount";
              updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000);
              updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000);
              updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
              updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
              updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeId, SqlDbType.UniqueIdentifier);
              updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt);
              updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
              this.UpdateScopeInfoCommand = updReplicaInfoCmd;

              //
              // Select overlapping scopes
              //
              // get tombstones for cleanup

              SqlCommand overlappingScopesCmd = new SqlCommand();
              overlappingScopesCmd.CommandType = CommandType.Text;
              overlappingScopesCmd.CommandText =@"select scopeTableMap2.table_name as sync_table_name,
                                                  scopeTableMap2.scope_name as sync_shared_scope_name
                                                  from scope_table_map scopeTableMap1
                                                  join scope_table_map scopeTableMap2
                                                  on scopeTableMap1.table_name = scopeTableMap2.table_name
                                                  and scopeTableMap1.scope_name = @sync_scope_name
                                                  where scopeTableMap2.scope_name <> @sync_scope_name;";
              overlappingScopesCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
              this.SelectOverlappingScopesCommand = overlappingScopesCmd;
             
              //
              // Update table cleanup info
              //
              SqlCommand updScopeCleanupInfoCmd = new SqlCommand();
              updScopeCleanupInfoCmd.CommandType = CommandType.Text;
              updScopeCleanupInfoCmd.CommandText = "update  scope_info set " +
                                              "scope_cleanup_timestamp = @" + DbSyncSession.SyncScopeCleanupTimestamp + " " +
                                              "where scope_name = @" + DbSyncSession.SyncScopeName + " and " +
                                              "(scope_cleanup_timestamp is null or scope_cleanup_timestamp <  @" + DbSyncSession.SyncScopeCleanupTimestamp + ");" +
                                              "set @" + DbSyncSession.SyncRowCount + " = @@rowcount";
              updScopeCleanupInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupTimestamp, SqlDbType.BigInt);
              updScopeCleanupInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
              updScopeCleanupInfoCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
              this.UpdateScopeCleanupTimestampCommand = updScopeCleanupInfoCmd;

  }

  Can anybody help me??

  Thanks

  Mark Kang

  Friday, January 21, 2011 6:09 AM