none
The _selectchanges stored procedures is always called with sync_min_timestamp= 9223372036854775807 RRS feed

  • Question

  • 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

Answers

  • Mark,

    Can you check that the scope id on both the source and destination replicas are the same, and post the destination knowledge here so that we can inspect it?

    Thanks,

    Minh.

    Saturday, February 5, 2011 1:42 AM

All replies

  • Mark,

    Can you check that the scope id on both the source and destination replicas are the same, and post the destination knowledge here so that we can inspect it?

    Thanks,

    Minh.

    Saturday, February 5, 2011 1:42 AM
  • Hi Minh.

    You are right. It is because the scope id on both the source and destination replicas are the same. I have fixed it.

    Thanks

    Mark

    • Marked as answer by mark kang Thursday, February 24, 2011 8:08 AM
    • Unmarked as answer by mark kang Thursday, February 24, 2011 8:08 AM
    Wednesday, February 9, 2011 8:40 AM