locked
adapter commands comparison RRS feed

  • Question

  • Hi,

    I was comparing the sql commands generated by SqlSyncAdapterBuilder, with integrated change tracking, and those created for a server side using the Visual Studio wizard (DbServerSyncProvider, with integrated sql server change tracking too), and found that, besides a few syntax differences (like using dbo.MyTable instead of [MyTable]) all the commands are functionaly the same, except only one: the SelectIncrementalInsertsCommand:

    example

    with SqlSyncAdapterBuilder:
    IF @sync_initialized = 0
        SELECT [AccountStatuses].[AccountStatusId],
               [Code],
               [Description]
        FROM   [AccountStatuses];
    ELSE
        BEGIN
            SELECT [AccountStatuses].[AccountStatusId],
                   [Code],
                   [Description]
            FROM   [AccountStatuses] INNER JOIN
                   CHANGETABLE(CHANGES [AccountStatuses], @sync_last_received_anchor) AS CT
                   ON CT.[AccountStatusId] = [AccountStatuses].[AccountStatusId]
            WHERE  (CT.SYS_CHANGE_OPERATION = 'I'
                    AND CT.SYS_CHANGE_CREATION_VERSION <= @sync_new_received_anchor
                    AND (CT.SYS_CHANGE_CONTEXT IS NULL
                         OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary));
            IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'[AccountStatuses]')) > @sync_last_received_anchor
                RAISERROR (N'SQL Server change tracking has cleaned up tracking information for table ''%s''. To recover from this error, the client must reinitialize its local database and try to synchronize again.', 16, 3, N'[AccountStatuses]');
        END
    Now, with VS wizard (server side, using DbServerSyncProvider)
    IF @sync_initialized = 0
    SELECT dbo.AccountStatuses.[AccountStatusId],
    [Code],
    [Description]
    FROM dbo.AccountStatuses LEFT OUTER JOIN
    CHANGETABLE(CHANGES dbo.AccountStatuses, @sync_last_received_anchor) AS CT
    ON CT.[AccountStatusId] = dbo.AccountStatuses.[AccountStatusId]
    WHERE (CT.SYS_CHANGE_CONTEXT IS NULL
    OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary);

    ELSE
    BEGIN
    SELECT dbo.AccountStatuses.[AccountStatusId],
    [Code],
    [Description]
    FROM dbo.AccountStatuses INNER JOIN
    CHANGETABLE(CHANGES dbo.AccountStatuses, @sync_last_received_anchor) AS CT
    ON CT.[AccountStatusId] = dbo.AccountStatuses.[AccountStatusId]
    WHERE (CT.SYS_CHANGE_OPERATION = 'I'
    AND CT.SYS_CHANGE_CREATION_VERSION <= @sync_new_received_anchor
    AND (CT.SYS_CHANGE_CONTEXT IS NULL
    OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary));
    IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.AccountStatuses')) > @sync_last_received_anchor
    RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. To recover from this error, the client must reinitialize its local database and try again', 16, 3, N'dbo.AccountStatuses');
    END

    Is there any explanation as for why it is doing this? and also, why is this the only command that differs from those generated by the command builder manually? (i.e, updates, deletes, conflicts..).


    Thanks!












    C# + BizTalk2009 Developer
    Friday, October 30, 2009 7:46 PM

Answers

  • Thanks for raising this up.

    I guess you must use the sync Services for Ado.net V3 version ( which is shipped with the MS sync framework V2 ). there is an optimization we did in the V3 release ( removed the join clause for the inital sync ). the VS still uses the previously released SyncService, hence the difference.

    functionalility wise, as you already found out,  both queries are the same.

    Thanks
    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, November 13, 2009 5:47 PM
    Moderator