none
InvalidCastException with batched changes

    Question

  • Platform: SQL Server 2008 R2, .NET Framework 4, Sync Framework 2.1.

    I am trying to set up batching on a DbServerSyncProvider, following the instructions on MSDN.

    Here is the code used to set up my SelectNewAnchorCommand:

                selectNewAnchorCommand = new SqlCommand("NewSyncBatchAnchor")
                {
                    CommandType = CommandType.StoredProcedure,
                };
                selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncMaxReceivedAnchor, SqlDbType.Timestamp).Direction = ParameterDirection.InputOutput;
                selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
                selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncBatchSize, SqlDbType.BigInt);
                selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp).Direction = ParameterDirection.Output;
                selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncBatchCount, SqlDbType.BigInt).Direction = ParameterDirection.InputOutput;
                return selectNewAnchorCommand;

    Once I have the command, I set up the provider:

                DbServerSyncProvider syncProvider = new DbServerSyncProvider
                {
                    Connection = Connection,
                    SelectNewAnchorCommand = GetSelectNewAnchorCommand(),
                    BatchSize = batchSize,
                };
                syncProvider.SyncAdapters.AddRange(TableNames.Select(CreateAdapter));
                return syncProvider;

    The command works by invoking a stored procedure on the server:

    ALTER procedure [dbo].[NewSyncBatchAnchor] (
            @sync_last_received_anchor timestamp ,
            @sync_batch_size BIGINT,
            @sync_max_received_anchor timestamp output,
            @sync_new_received_anchor timestamp output,          
            @sync_batch_count BIGINT output)      
    as            
          if @sync_batch_size <= 0
                set @sync_batch_size = 1000     
    
          if @sync_max_received_anchor is null
              set @sync_max_received_anchor = @@DBTS -- use "min_active_rowversion()-1" if you can
    
          -- simplest form of batching
          if @sync_last_received_anchor is null or @sync_last_received_anchor = 0
          begin            
              set @sync_new_received_anchor = @sync_batch_size
                if @sync_batch_count <= 0
                      set @sync_batch_count = (@sync_max_received_anchor /  @sync_batch_size) + 1
          end
          else
          begin
              set @sync_new_received_anchor = @sync_last_received_anchor + @sync_batch_size
                if @sync_batch_count <= 0
                      set @sync_batch_count = (@sync_max_received_anchor /  @sync_batch_size) -
                                              (@sync_new_received_anchor /  @sync_batch_size) + 1
          end        
    
        -- check if this is the last batch       
        if @sync_new_received_anchor >= @sync_max_received_anchor
        begin
            set @sync_new_received_anchor = @sync_max_received_anchor       
                if @sync_batch_count <= 0
                      set @sync_batch_count = 1
        end 

    When I try to synchronize I get an InvalidCastException "Failed to convert parameter value from a Int64 to a Byte[]."

    The relevant stack trace is:

       at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType)
       at System.Data.SqlClient.SqlParameter.GetCoercedValue()
       at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
       at System.Data.SqlClient.SqlCommand.SetUpRPCParameters(_SqlRPC rpc, Int32 startCount, Boolean inSchema, SqlParameterCollection parameters)
       at System.Data.SqlClient.SqlCommand.BuildRPC(Boolean inSchema, SqlParameterCollection parameters, _SqlRPC& rpc)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at Microsoft.Synchronization.Data.Server.DbServerSyncProvider.GetNewServerAnchor(SyncGroupMetadata groupMetadata, SyncSession session)
       at Microsoft.Synchronization.Data.Server.DbServerSyncProvider.GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession)

    Thursday, September 12, 2013 2:00 AM

All replies

  • why is your SyncMaxReceivedAnchor InputOutput?
    Saturday, September 14, 2013 1:11 AM
    Moderator
  • Thank you very much for taking time to respond to my question!

    There is no particular reason why SyncMaxReceivedAnchor is InputOutput. I think it was an artifact of a code sample I used before I tracked down the MSDN article.

    I tried modifying the code so that all the InputOutput parameters are Output instead:

                selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncMaxReceivedAnchor, SqlDbType.Timestamp).Direction = ParameterDirection.Output;
                selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
                selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncBatchSize, SqlDbType.BigInt);
                selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp).Direction = ParameterDirection.Output;
                selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncBatchCount, SqlDbType.BigInt).Direction = ParameterDirection.Output;

    This failed with the same InvalidCastException as before: "Failed to convert parameter value from a Int64 to a Byte[]."

    It looks like the stack trace is the same, but I will post it anyway in case I'm wrong:

       at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType)
       at System.Data.SqlClient.SqlParameter.GetCoercedValue()
       at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
       at System.Data.SqlClient.SqlCommand.SetUpRPCParameters(_SqlRPC rpc, Int32 startCount, Boolean inSchema, SqlParameterCollection parameters)
       at System.Data.SqlClient.SqlCommand.BuildRPC(Boolean inSchema, SqlParameterCollection parameters, _SqlRPC& rpc)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at Microsoft.Synchronization.Data.Server.DbServerSyncProvider.GetNewServerAnchor(SyncGroupMetadata groupMetadata, SyncSession session)
       at Microsoft.Synchronization.Data.Server.DbServerSyncProvider.GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession)

    I just noticed that there is an inner exception, which is also an InvalidCastException: "Invalid cast from 'System.Int64' to 'System.Byte[]'." Here is the stack trace for the inner exception:

       at System.Convert.DefaultToType(IConvertible value, Type targetType, IFormatProvider provider)
       at System.Int64.System.IConvertible.ToType(Type type, IFormatProvider provider)
       at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
       at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType)

    Tuesday, September 17, 2013 6:15 PM
  • Looks like using BigInt when defining the stored procedure fixes the problem in this case. I am s

                selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncMaxReceivedAnchor, SqlDbType.BigInt).Direction = ParameterDirection.Output;
                selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt);
                selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncBatchSize, SqlDbType.BigInt);
                selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.BigInt).Direction = ParameterDirection.Output;
                selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncBatchCount, SqlDbType.BigInt).Direction = ParameterDirection.Output;

    I am scratching my head a bit as to why you can't pass a T-SQL timestamp parameter into a C# SqlParameter that is defined to be SqlDbType.Timestamp.

    Tuesday, September 17, 2013 8:43 PM