I had to make a few changes in my version:
- parameters are defined as "timestamp" data type, changed them to "binary(8)"
- for consistency I changed the "out" suffix on 2 of the 3 output params to be "output" like the 3rd one
- multiple checks of "IF @sync_batch_count <= 0" need to be replaced with "IF @sync_batch_count IS NULL OR @sync_batch_count <= 0"
SELECT @sync_last_received_anchor = MIN(TimestampCol) FROM ( SELECT MIN(CreateVersion) AS TimestampCol FROM dbo.BillingInfo UNION SELECT MIN(UpdateVersion) AS TimestampCol FROM dbo.BillingInfo UNION SELECT MIN(CreateVersion) AS TimestampCol FROM dbo.Employees UNION SELECT MIN(UpdateVersion) AS TimestampCol FROM dbo.Employees UNION SELECT MIN(CreateVersion) AS TimestampCol FROM dbo.Projects UNION SELECT MIN(UpdateVersion) AS TimestampCol FROM dbo.Projects UNION SELECT MIN(CreateVersion) AS TimestampCol FROM dbo.TimeEntries UNION SELECT MIN(UpdateVersion) AS TimestampCol FROM dbo.TimeEntries UNION SELECT MIN(DeleteVersion) AS TimestampCol FROM dbo.TimeEntries_Tombstone ) MinTimestamp
Before I made the above changes I was getting erratic behaviour depending on the batch size specified. Now everything seems to be working as expected.
4 comments:
I was struggling with the same issue and i followed your solution. Looks like its working fine now..Thanks a lot for the tip.
Thanks - I've tearing my hair out at sync services for the past week trying to get batching working correctly... The original version of the proc doesn't seem to like batching empty tables (gets in a loop) Hopefully, this will fix it and many thanks if it does.
No, didn't seem to work for me. I have multiple sync groups and the anchor is constant across the whole session; does anyone know if this would affect the ability for batching to work?
If I view what is going on in SQL Profiler, the same table is trying to be batched dozens of times as if it is caught in a loop. I'm wondering because I have muliple sync groups, whether the anchor is 'valid' for every sync group.
Thanks, I was battling this monster all day and your post fixed it.
Post a Comment