Asked by:
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
-
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