Wednesday, July 2, 2008

Sync for ADO.NET CTP2 - Batch Anchor proc is broken?

In the documentation for the CTP2 it gives a sample stored procedure called usp_GetNewBatchAnchor, but from my tests it doesn't seem to work.
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"
NOTE: you'll also need to change the tables it's using to check for the min timestamp value, in my case the following:
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:

Stewie said...

I was struggling with the same issue and i followed your solution. Looks like its working fine now..Thanks a lot for the tip.

Jon said...

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.

Jon said...

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.

accomplice said...

Thanks, I was battling this monster all day and your post fixed it.