Here is a stored procedure I developed that is modified from several things I found in these forums. I hope it helps someone trying to do batching with DateTime using syncservices pre 4.x.
CREATE PROCEDURE
dbo.sp_new_batch_anchor_POIM (
@sync_last_received_anchor
datetime
,
@sync_batch_size
Int
,
@sync_max_received_anchor
datetime output
,
@sync_new_received_anchor
datetime output
,
@sync_batch_count
Int output
)
as
declare
@minDateTime
datetime
declare
@maxDateTime
datetime
declare
@rowCount
Int
declare
@batchSize
Int
set
@batchSize = 5000
select
@minDateTime = (select
min (CreationDate)
from
POItemMaster)
-- Set a default batch size if a valid one is not passed in.
IF
@sync_batch_size
IS NULL OR
@sync_batch_size <= 0
SET
@sync_batch_size = 1
IF
@sync_last_received_anchor
IS NULL or
@sync_last_received_anchor < @minDateTime
SET
@sync_last_received_anchor = @minDateTime
--insert into SyncLog (SyncText) values
('Sinc last received anchor: ' + convert(varchar(255), @sync_last_received_anchor,
109) )
select
@maxDateTime = (SELECT
MAX(lasteditdate)
AS
max_lastedit_date
FROM
(SELECT
TOP
(@batchSize) lasteditdate
FROM
POItemMaster
AS
b
WHERE
(lasteditdate >= @sync_last_received_anchor)
ORDER BY
lasteditdate)
AS
derivedtbl_1)
if
@sync_last_received_anchor < @maxDateTime
BEGIN
IF
@sync_max_received_anchor
IS NULL
select
@sync_max_received_anchor = (select
max (lasteditdate)
from
POItemMaster)
-- insert into SyncLog (SyncText) values
('Sinc last received anchor: ' + convert(varchar(255), @sync_last_received_anchor,
109) )
--
insert
into SyncLog (SyncText) values ('Sinc new max received anchor: ' + convert(varchar(255),
@maxDateTime,
109) )
SET
@sync_new_received_anchor = @maxDateTime
--insert into SyncLog (SyncText) values
('Sinc new received anchor: ' + convert(varchar(255), @sync_new_received_anchor)
)
IF
@sync_batch_count <= 0
begin
select
@rowCount = (select
count(*)
from
POItemMaster)
select
@sync_batch_count = (@rowCount / @batchSize) +1
end
-- insert into SyncLog (SyncText) values
('Sinc batchcount: ' + convert(varchar(255), @sync_batch_count)
)
-- Check whether this is the last batch.
IF
@sync_new_received_anchor >= @sync_max_received_anchor
BEGIN
select
@sync_new_received_anchor = @sync_max_received_anchor
END
END
ELSE
BEGIN
set
@sync_max_received_anchor =
GETUTCDATE
();
set
@sync_new_received_anchor = @sync_max_received_anchor;
END
