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)
