locked
Adding BatchSize to my Synchronization RRS feed

  • Question

  • Hello,

    I'm trying to hook up BatchSize to my Synchronization. Without Batching and using the following SelectNewAnchorCommand, synchronization is working fine.

    SqlCommand anchorCmd = new SqlCommand();

                    string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;

                    anchorCmd.CommandType = CommandType.Text;

                    anchorCmd.CommandText = "Select " + newAnchorVariable + " = min_active_rowversion() - 1"; //@@DBTS";  // for SQL Server 2005 SP2, use "min_active_rowversion() - 1"

                    anchorCmd.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp, 8);

                    anchorCmd.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;

                    anchorCmd.Connection = serverConnection;

                    this.SelectNewAnchorCommand = anchorCmd;

    Now, when I try to use the following SelectNewAnchor command accompanied with the below stored procedure; I enter into a almost endless loop.

    SqlCommand anchorCmd = new SqlCommand();

                    anchorCmd.CommandType = CommandType.StoredProcedure;

                    anchorCmd.CommandText = Constants.spLTSSGNewBatchAnchor;

                    anchorCmd.Parameters.Add("@" + SyncSession.SyncMaxReceivedAnchor, SqlDbType.Timestamp).Direction = ParameterDirection.InputOutput;

                    anchorCmd.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);

                    anchorCmd.Parameters.Add("@" + SyncSession.SyncBatchSize, SqlDbType.Int);

                    anchorCmd.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp).Direction = ParameterDirection.Output;

                    anchorCmd.Parameters.Add("@" + SyncSession.SyncBatchCount, SqlDbType.Int).Direction = ParameterDirection.InputOutput;

                    this.SelectNewAnchorCommand = anchorCmd;

                    this.BatchSize = _CurrentSyncScenario.BatchSize;

    Here is the SP I am using:

    USE [TempTestBatch]

    GO

    /****** Object:  StoredProcedure [dbo].[sp_LTS_SG_New_Batch_Anchor]    Script Date: 11/20/2012 19:58:32 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [dbo].[sp_LTS_SG_New_Batch_Anchor]

           @sync_last_received_anchor [timestamp],

           @sync_batch_size [int],

           @sync_max_received_anchor [timestamp] OUTPUT,

           @sync_new_received_anchor [timestamp] OUTPUT,

           @sync_batch_count [int] OUTPUT

    AS

     if @sync_batch_size is null or @sync_batch_size <= 0

    set @sync_batch_size = 1000

    if @sync_max_received_anchor is null

    set @sync_max_received_anchor = min_active_rowversion() - 1

    -- 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

    I cannont find what is wrong with the Stored Procedure or SelectNewAnchorCommand.

    Any help will be greatly appreciated.

    Thanks,

    Ken


    Ken

    Wednesday, November 21, 2012 4:20 AM