none
ApplyChangeFailed 事件不被触发 RRS feed

  • Question

  • 从Sql Server 2005 到Sql Server 2005的同步(单向上传)是正常的,当目标数据库为Oracle时,(单向上传)不会触发ApplyChangeFailed事件。

    SQL同步时的信息:

    VERBOSE, WcfSvcHost, 8, 06/16/2010 13:40:41:343,    RowId: orders-1 UV: 1,78012 CV: 1,78011 IsTomb: False
    VERBOSE, WcfSvcHost, 8, 06/16/2010 13:40:41:343,    Updating row with PK: order_id="1"  on peer2
    INFO   , WcfSvcHost, 8, 06/16/2010 13:40:41:359, Optimistic change application failed
    VERBOSE, WcfSvcHost, 8, 06/16/2010 13:40:41:359,    Executing Command: orders_SelectRow
    VERBOSE, WcfSvcHost, 8, 06/16/2010 13:40:41:359,       Parameter: order_id Value: 1
    VERBOSE, WcfSvcHost, 8, 06/16/2010 13:40:41:359,       Parameter: @sync_scope_local_id Value: 12
    VERBOSE, WcfSvcHost, 8, 06/16/2010 13:40:41:359,
    VERBOSE, WcfSvcHost, 8, 06/16/2010 13:40:41:359,    RowId: orders-1 UV: 1,78011 CV: 1,78011 IsTomb: False
    VERBOSE, WcfSvcHost, 8, 06/16/2010 13:40:41:359,             Checking for conflict.
    VERBOSE, WcfSvcHost, 8, 06/16/2010 13:40:41:359,             Remote peer contains local change. Returning RemoteSupersedes.
    VERBOSE, WcfSvcHost, 8, 06/16/2010 13:40:41:375,    Executing Command: orders_Update

    VERBOSE, WcfSvcHost, 8, 06/16/2010 13:40:41:375,       Parameter: order_id Value: 1
    VERBOSE, WcfSvcHost, 8, 06/16/2010 13:40:41:375,       Parameter: order_date Value: 2009-10-9 0:00:00
    VERBOSE, WcfSvcHost, 8, 06/16/2010 13:40:41:375,       Parameter: @sync_min_timestamp Value: 86021
    VERBOSE, WcfSvcHost, 8, 06/16/2010 13:40:41:375,       Parameter: @sync_row_count Value: Skipped since Not Input/InputOutput
    VERBOSE, WcfSvcHost, 8, 06/16/2010 13:40:41:375,       Parameter: @sync_force_write Value: 0
    VERBOSE, WcfSvcHost, 8, 06/16/2010 13:40:41:375,    Rows affected: 1
    VERBOSE, WcfSvcHost, 8, 06/16/2010 13:40:41:375,    Executing Command: orders_UpdateMetadata
    VERBOSE, WcfSvcHost, 8, 06/16/2010 13:40:41:375,       Parameter: order_id Value: 1
    VERBOSE, WcfSvcHost, 8, 06/16/2010 13:40:41:375,       Parameter: @sync_scope_local_id Value: 12
    VERBOSE, WcfSvcHost, 8, 06/16/2010 13:40:41:375,       Parameter: @sync_row_timestamp Value: 86021
    VERBOSE, WcfSvcHost, 8, 06/16/2010 13:40:41:375,       Parameter: @sync_create_peer_key Value: 1
    VERBOSE, WcfSvcHost, 8, 06/16/2010 13:40:41:375,       Parameter: @sync_create_peer_timestamp Value: 78011
    VERBOSE, WcfSvcHost, 8, 06/16/2010 13:40:41:375,       Parameter: @sync_update_peer_key Value: 1
    VERBOSE, WcfSvcHost, 8, 06/16/2010 13:40:41:375,       Parameter: @sync_update_peer_timestamp Value: 78012
    VERBOSE, WcfSvcHost, 8, 06/16/2010 13:40:41:375,       Parameter: @sync_row_is_tombstone Value: 0
    VERBOSE, WcfSvcHost, 8, 06/16/2010 13:40:41:375,       Parameter: @sync_check_concurrency Value: 0
    VERBOSE, WcfSvcHost, 8, 06/16/2010 13:40:41:375,       Parameter: @sync_row_count Value: Skipped since Not Input/InputOutput
    VERBOSE, WcfSvcHost, 8, 06/16/2010 13:40:41:375,    Rows affected: 1

    Oracle同步时的信息:

    VERBOSE, WcfSvcHost, 8, 06/17/2010 07:50:46:703,    RowId: orders-9 UV: 1,82028 CV: 1,82027 IsTomb: False
    VERBOSE, WcfSvcHost, 8, 06/17/2010 07:50:46:703,    Updating row with PK: ORDER_ID="9"  on
    INFO   , WcfSvcHost, 8, 06/17/2010 07:50:46:703, Optimistic change application failed
    ERROR  , WcfSvcHost, 8, 06/17/2010 07:50:46:703, Caught exception while enumerating changes
    Microsoft.Synchronization.Data.DbSyncException: 找不到要为命令参数“selectedRow”设置的值。请确保要同步的表的架构是一致的,并且确保此作用域的配置正确。
       在 Microsoft.Synchronization.Data.DbChangeHandler.SetColumnParameters(IDbCommand command, SyncRowMetadata rowMetadata)
       在 Microsoft.Synchronization.Data.DbChangeHandler.ExecuteSelectRowCommand(SyncRowMetadata rowMetadata, DataTable& dataTable)

    ERROR  , WcfSvcHost, 8, 06/17/2010 07:50:46:718, Caught Exception during SelectRowCommand
    Microsoft.Synchronization.Data.DbSyncException: 找不到要为命令参数“selectedRow”设置的值。请确保要同步的表的架构是一致的,并且确保此作用域的配置正确。
       在 Microsoft.Synchronization.Data.DbChangeHandler.SetColumnParameters(IDbCommand command, SyncRowMetadata rowMetadata)
       在 Microsoft.Synchronization.Data.DbChangeHandler.ExecuteSelectRowCommand(SyncRowMetadata rowMetadata, DataTable& dataTable)
       在 Microsoft.Synchronization.Data.DbChangeHandler.ApplyUpdate()

    VERBOSE, WcfSvcHost, 8, 06/17/2010 07:50:46:718,       Raising Apply Metadata Failed Event

     

    Oracle数据库时,ApplyChangeFailed不会被触发,而是直接触发ApplyMetadataFailed。

    不知道是什么原因?

     

    Thursday, June 17, 2010 8:25 AM

All replies

  • From the trace, the conflict mechanism has kicked in, the SelectRowCommand is being executed. However, there is a error in executing the command, due to "selectedRow" parameter is not set. Later, an ApplyMetadataFailed is raised due to the failure above.

    So please double check whether ApplyChangeFailed is raised and check the invocation of the stored procedure for the selectRowCommand and see whether everything is correct.

    Thursday, June 17, 2010 5:33 PM
    Answerer
  • Thank you,Jin H.

    ApplyChangeFailed event is not triggered,selectRowCommand is ok.

    This problem occurs only at the one-way From the target database to Sql Server, it will automatically detect ApplyChangeFailed (Checking for conflict). Target database is Oracle, they will skip ApplyChangeFailed, error information: Could not find parameters for the command "selectedRow" set of values.

    Exception Info: at Microsoft.Synchronization.Data.DbChangeHandler.SetColumnParameters (IDbCommand command, SyncRowMetadata rowMetadata)
       
    at Microsoft.Synchronization.Data.DbChangeHandler.ExecuteSelectRowCommand (SyncRowMetadata rowMetadata, DataTable & dataTable)
       
    at Microsoft.Synchronization.Data.DbChangeHandler.ApplyUpdate ()

     internal int ExecuteSelectRowCommand(SyncRowMetadata rowMetadata, out DataTable dataTable)
            {
                string selectRowCommandName = DbSyncAdapter.SelectRowCommandName;
                IDbCommand selectRowCommand = base.Adapter.SelectRowCommand;
                int num = 0;
                IDataReader dataReader = null;
                dataTable = new DataTable(base.Adapter.TableName);
                dataTable.Locale = CultureInfo.InvariantCulture;
                try
                {
                    if (selectRowCommand == null)
                    {
                        SyncTracer.Error("Missing command '{0}' ", new object[] { selectRowCommandName });
                        throw SyncExptEx.MissingCommand(selectRowCommandName, base.Adapter.TableName, DbSyncProvider.ClassName, "http://msdn.microsoft.com/sync");
                    }
                    this.SetColumnParameters(selectRowCommand, rowMetadata);
                    SyncTracer.TraceCommandAndParameters(selectRowCommand);
                    dataReader = selectRowCommand.ExecuteReader();
                    num = new SyncDbAdapter().FillFromReader(dataTable, dataReader);
                }
                catch (Exception exception)
                {
                    SyncTracer.Error("Caught exception while enumerating changes\n{0}\n", new object[] { exception });
                    throw;
                }
                finally
                {
                    if (dataReader != null)
                    {
                        dataReader.Close();
                    }
                }
                return num;
            }

    selectedRow parameter is an Out parameter.

    Friday, June 18, 2010 1:30 AM
  •                 OracleCommand selRowOrdersCmd = new OracleCommand();
                    selRowOrdersCmd.CommandType = CommandType.StoredProcedure;
                    selRowOrdersCmd.CommandText = "sp_" + SyncAdapterTables[i].basetable + "_selectrow";

                    foreach (var pk in SyncAdapterTables[i].pkcols)
                    {
                        Utility.CreateOrclCmdParameter(selRowOrdersCmd, pk);
                    }
                   
                    selRowOrdersCmd.Parameters.Add(DbSyncSession.SyncScopeLocalId, OracleType.Int32);
                    selRowOrdersCmd.Parameters.Add("selectedRow", OracleType.Cursor).Direction = ParameterDirection.Output;

                    adapter.SelectRowCommand = selRowOrdersCmd;

    Friday, June 18, 2010 2:37 AM
  • Problem has been solved, the parameters of the stored procedure in question;
    In this way, the not found "selectRow" parameter. "SelectRow" is not a system-wide default parameters, use the "sync_row_count" replace the parameter, it will not report this error.

    selRowOrdersCmd.Parameters.Add("selectedRow", OracleType.Cursor).Direction = ParameterDirection.Output;

    selRowOrdersCmd.Parameters.Add("sync_row_count", OracleType.Cursor).Direction = ParameterDirection.Output;

    internal override void SetColumnParameters(IDbCommand command, SyncRowMetadata rowMetadata)
            {
                DataRow row = rowMetadata.Row;
                Dictionary<string, string> dictionary = base.Adapter.RemoteToLocalMap();
                foreach (DbParameter parameter in command.Parameters)
                {
                    object replicaKey = null;
                    switch (parameter.ParameterName)
                    {
                        case "@sync_update_peer_key":
                        case ":sync_update_peer_key":
                        case "sync_update_peer_key":
                            replicaKey = rowMetadata.UpdateVersion.ReplicaKey;
                            break;

                        case "@sync_update_peer_timestamp":
                        case ":sync_update_peer_timestamp":
                        case "sync_update_peer_timestamp":
                            replicaKey = rowMetadata.UpdateVersion.TickCount;
                            break;

                        case "@sync_create_peer_key":
                        case ":sync_create_peer_key":
                        case "sync_create_peer_key":
                            replicaKey = rowMetadata.CreateVersion.ReplicaKey;
                            break;

                        case "@sync_create_peer_timestamp":
                        case ":sync_create_peer_timestamp":
                        case "sync_create_peer_timestamp":
                            replicaKey = rowMetadata.CreateVersion.TickCount;
                            break;

                        case "@sync_row_timestamp":
                        case ":sync_row_timestamp":
                        case "sync_row_timestamp":
                            replicaKey = rowMetadata.LocalTimestamp;
                            break;

                        case "@sync_row_is_tombstone":
                        case ":sync_row_is_tombstone":
                        case "sync_row_is_tombstone":
                            replicaKey = rowMetadata.IsTombstone ? 1 : 0;
                            break;

                        case "@sync_stage_name":
                        case ":sync_stage_name":
                        case "sync_stage_name":
                        case "@sync_session_id":
                        case ":sync_session_id":
                        case "sync_session_id":
                        case "@sync_new_timestamp":
                        case ":sync_new_timestamp":
                        case "sync_new_timestamp":
                        case "@sync_scope_timestamp":
                        case ":sync_scope_timestamp":
                        case "sync_scope_timestamp":
                        case "@sync_scope_restore_count":
                        case ":sync_scope_restore_count":
                        case "sync_scope_restore_count":
                        case "@sync_scope_name":
                        case ":sync_scope_name":
                        case "sync_scope_name":
                        case "@sync_scope_cleanup_knowledge":
                        case ":sync_scope_cleanup_knowledge":
                        case "sync_scope_cleanup_knowledge":
                        case "@sync_scope_knowledge":
                        case ":sync_scope_knowledge":
                        case "sync_scope_knowledge":
                        case "@sync_initialize":
                        case ":sync_initialize":
                        case "sync_initialize":
                        case "@sync_scope_id":
                        case ":sync_scope_id":
                        case "sync_scope_id":
                        case "@sync_scope_local_id":
                        case ":sync_scope_local_id":
                        case "sync_scope_local_id":
                        case "@sync_force_write":
                        case ":sync_force_write":
                        case "sync_force_write":
                        case "@sync_min_timestamp":
                        case ":sync_min_timestamp":
                        case "sync_min_timestamp":
                        case "@sync_check_concurrency":
                        case ":sync_check_concurrency":
                        case "sync_check_concurrency":
                        case "@sync_row_count":
                        case ":sync_row_count":
                        case "sync_row_count":
                        {
                            continue;
                        }
                        default:
                            if (!string.IsNullOrEmpty(parameter.SourceColumn))
                            {
                                if (!row.Table.Columns.Contains(parameter.SourceColumn))
                                {
                                    string remoteColumnFromLocalColumn = base.Adapter.GetRemoteColumnFromLocalColumn(parameter.SourceColumn);
                                    if (row.Table.Columns.Contains(remoteColumnFromLocalColumn))
                                    {
                                        parameter.SourceColumn = remoteColumnFromLocalColumn;
                                    }
                                }
                                replicaKey = row[parameter.SourceColumn];
                            }
                            break;
                    }
                    if (replicaKey == null)
                    {
                        foreach (DataColumn column in row.Table.Columns)
                        {
                            string columnName = null;
                            string parameterName = null;
                            if ((dictionary == null) || !dictionary.TryGetValue(column.ColumnName, out columnName))
                            {
                                columnName = column.ColumnName;
                            }
                            parameterName = ManagementUtils.ParameterNameForColumn(columnName);
                            if (parameter == SyncUtil.GetParameter(command, parameterName))
                            {
                                parameter.SourceColumn = column.ColumnName;
                                replicaKey = row[column];
                                break;
                            }
                        }
                    }
                    if (replicaKey == null)
                    {
                        throw new DbSyncException(SyncResource.FormatString("SqlCeProviderParameterMissing", new object[] { parameter.ParameterName }));
                    }
                    parameter.Value = replicaKey;
                }
            }

    Thank you,Jin H.

    By the way, Why did Microsoft do not provide the source code?

    Friday, June 18, 2010 3:22 AM
  • Thanks for the investigation.

    Can you post your oracle sp_...selectrow stored procedure code?

    I checked the Oracle sample at http://code.msdn.microsoft.com/Release/ProjectReleases.aspx?ProjectName=sync&ReleaseId=3761 but didn't find use of sync_row_count parameter in the sp_...selectRow stored procedure code.

    Friday, June 18, 2010 6:23 PM
    Answerer
  • Using the parameter names "sync_row_count" instead of "selectedRow", to the "SetColumnParameters" method will not be assigned, it will not be an error: Can not find parameters for the command "selectedRow" set of values.
    Earlier I said may not be clear, I'm sorry.

    create or replace procedure sp_ORDERS_selectrow(
            ORDER_ID NUMBER,
            sync_scope_local_id IN int,
            sync_row_count OUT sys_refcursor)
    IS
    BEGIN
     DECLARE
          p_ORDER_ID NUMBER := ORDER_ID;
          BEGIN
      open sync_row_count for select
       tt.ORDER_ID,
                bt.ORDER_DATE,
                tt.sync_row_is_tombstone,
                tt.local_update_peer_timestamp as sync_row_timestamp,
                case when (tt.update_scope_local_id is null or tt.update_scope_local_id <> sync_scope_local_id)
                     then case when (tt.restore_timestamp is null) then tt.local_update_peer_timestamp else tt.restore_timestamp end else tt.scope_update_peer_timestamp end as sync_update_peer_timestamp,
                case when (tt.update_scope_local_id is null or tt.update_scope_local_id <> sync_scope_local_id)
                     then tt.local_update_peer_key else tt.scope_update_peer_key end as sync_update_peer_key,
                case when (tt.create_scope_local_id is null or tt.create_scope_local_id <> sync_scope_local_id)
                     then tt.local_create_peer_timestamp else tt.scope_create_peer_timestamp end as sync_create_peer_timestamp,
                case when (tt.create_scope_local_id is null or tt.create_scope_local_id <> sync_scope_local_id)
                     then tt.local_create_peer_key else tt.scope_create_peer_key end as sync_create_peer_key
      from ORDERS bt right join ORDERS_tracking tt on bt.ORDER_ID=tt.ORDER_ID
      where tt.ORDER_ID = p_ORDER_ID;
     END;
    END;

    • Proposed as answer by TPiec Monday, April 2, 2012 10:20 AM
    Monday, June 28, 2010 12:57 AM
  • Sorry I digg this out but I had similar problem.

    In my case I have conflict in Oracle DB (I caused it myself to chceck error handling). I had record which was deleted in MSSQL and was never present in Oracle DB. I used different name for Output Crusor to have different name to Integer value "sync_row_count OUT int" in other "sp_ *" eg. like in "_applyinsert". When I have changed column name to "sync_row_count" in _selectrow procedure all works as intened. (I hope).

    Monday, April 2, 2012 10:19 AM