locked
Possible BUG: SqlSyncAdapterBuilder with Change Tracking RRS feed

  • Question

  • Hi all,

    I am using SqlSyncAdapterBuilder with SQL 2008 Change Tracking.
    The generated syncClientAdapter.SelectIncrementalInsertsCommand is wrong. It doesn't use the PK when it joins to CHANGETABLE(...), and it also doesn't fully qualify certain fields, so I end up with the following error:

    Invalid column name 'ApplicationId'.
    Invalid column name 'LoweredUserName'.
    Ambiguous column name 'UserId'.
    Invalid column name 'ApplicationId'.
    Invalid column name 'LoweredUserName'.
    Ambiguous column name 'UserId'.


    I guess I'd like to know if someone has encountered such problem or have an insight of what my be going wrong.

    Here's all the details. This is my code:

    SqlSyncAdapterBuilder clientTableAdapterBuilder = new SqlSyncAdapterBuilder(_clientconn);

    clientTableAdapterBuilder.TableName = "aspnet_Users";

    clientTableAdapterBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;

    clientTableAdapterBuilder.SyncDirection = SyncDirection.Bidirectional;

    SyncAdapter syncClientAdapter = clientTableAdapterBuilder.ToSyncAdapter();

    _clientSyncProvider.SyncAdapters.Add(syncClientAdapter);



    I am able to extract the faulty statement while debugging the code and also using SQL Server Profiler - shown below, but first let me show you what its CHANGETABLE section returns:

    (extracted from the actual statement)
    SELECT * FROM CHANGETABLE(CHANGES [aspnet_Users], @sync_last_received_anchor) CT
    , where @sync_last_received_anchor is NULL
    returns 1 rows, correctly, which is the insert that needs to be synced:
    Return Result:
    (Columns:)
    SYS_CHANGE_VERSION, SYS_CHANGE_CREATION_VERSION, SYS_CHANGE_OPERATION, SYS_CHANGE_COLUMNS SYS_CHANGE_CONTEXT, UserId
    (Values:)
    191, NULL, 'U', NULL, NULL, '39F2A224-9EED-4024-BCFC-7C095845A8F1'

    Notice that CHANGETABLE correctly returns UserId as this is table's PK

    Now, please see what the SyncAdapterBuilder generated - notice how it's using non-existent columns in the CHANGETABLE join:
    IF @sync_initialized = 0 SELECT [aspnet_Users].[ApplicationId], [UserId], [UserName], [aspnet_Users].[LoweredUserName], [MobileAlias], [IsAnonymous], [LastActivityDate], [FirstName], [LastName], [HealthGroup], [PhoneNumber], [CellNumber], [PagerNumber] FROM [aspnet_Users] LEFT OUTER JOIN CHANGETABLE(CHANGES [aspnet_Users], @sync_last_received_anchor) CT ON CT.[ApplicationId] = [aspnet_Users].[ApplicationId] AND CT.[LoweredUserName] = [aspnet_Users].[LoweredUserName] WHERE (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary) ELSE BEGIN SELECT [aspnet_Users].[ApplicationId], [UserId], [UserName], [aspnet_Users].[LoweredUserName], [MobileAlias], [IsAnonymous], [LastActivityDate], [FirstName], [LastName], [HealthGroup], [PhoneNumber], [CellNumber], [PagerNumber] FROM [aspnet_Users] JOIN CHANGETABLE(CHANGES [aspnet_Users], @sync_last_received_anchor) CT ON CT.[ApplicationId] = [aspnet_Users].[ApplicationId] AND CT.[LoweredUserName] = [aspnet_Users].[LoweredUserName] 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'[aspnet_Users]')) > @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'[aspnet_Users]') END

    For reference, this is the table DDL:

    CREATE TABLE [dbo].[aspnet_Users](
     [ApplicationId] [uniqueidentifier] NOT NULL,
     [UserId] [uniqueidentifier] NOT NULL,
     [UserName] [nvarchar](256) NOT NULL,
     [LoweredUserName] [nvarchar](256) NOT NULL,
     [MobileAlias] [nvarchar](16) NULL,
     [IsAnonymous] [bit] NOT NULL,
     [LastActivityDate] [datetime] NOT NULL,
     [FirstName] [nvarchar](50) NULL,
     [LastName] [nvarchar](50) NULL,
     [HealthGroup] [nvarchar](50) NULL,
     [PhoneNumber] [nvarchar](13) NULL,
     [CellNumber] [nvarchar](13) NULL,
     [PagerNumber] [nvarchar](13) NULL,
     CONSTRAINT [PK__aspnet_Users__03317E3D] PRIMARY KEY NONCLUSTERED
    (
     [UserId] ASC
    )
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[aspnet_Users]  WITH CHECK ADD  CONSTRAINT [FK__aspnet_Us__Appli__0425A276] FOREIGN KEY([ApplicationId])
    REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
    GO

    ALTER TABLE [dbo].[aspnet_Users] CHECK CONSTRAINT [FK__aspnet_Us__Appli__0425A276]
    GO

    ALTER TABLE [dbo].[aspnet_Users] ADD  CONSTRAINT [DF__aspnet_Us__UserI__0519C6AF]  DEFAULT (newid()) FOR [UserId]
    GO

    ALTER TABLE [dbo].[aspnet_Users] ADD  CONSTRAINT [DF__aspnet_Us__Mobil__060DEAE8]  DEFAULT (NULL) FOR [MobileAlias]
    GO

    ALTER TABLE [dbo].[aspnet_Users] ADD  CONSTRAINT [DF__aspnet_Us__IsAno__07020F21]  DEFAULT ((0)) FOR [IsAnonymous]
    GO




     

    • Moved by Hengzhe Li Friday, April 22, 2011 5:31 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Wednesday, February 18, 2009 9:11 PM

Answers

  • It looks like SqlSyncAdapterBuilder is not using the PK of the table, but seeking *the* clustered unique constraint, which in this case is different than the PK constraint.

    In addition to the PK DDL shown above, the table also has this index:
    CREATE UNIQUE CLUSTERED INDEX [aspnet_Users_Index] ON [dbo].[aspnet_Users] (
    [ApplicationId] ASC,
    [LoweredUserName] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    Other than the obvios incompatibility with the internal SQL Chanhe Tracking (CHANGETABLE returns the correct PK - see post above), this also contradicts the documentation of SqlSyncAdapterBuilder:

    SqlSyncAdapterBuilder.RowGuidColumn Property
    Gets or sets a column of uniqueidentifier data type that is used to identify rows in the base table and the tombstone table.
    Namespace: Microsoft.Synchronization.Data.Server
    Assembly: Microsoft.Synchronization.Data.Server (in microsoft.synchronization.data.server.dll)
    Remarks

    To specify this property, a column that has the same name and the appropriate data type must be present in both the base table and the tombstone table. If this property is not specified, the primary keys of the tables are used to identify rows during synchronization.


    By the way, I tried to specify 
    mySqlSyncAdapterBuilder.RowGuidColumn = "userId";
    but that didn't work - generated SQL was still using [ApplicationId] and [LoweredUserName].

    • Marked as answer by G.Stoynev Tuesday, February 24, 2009 2:50 PM
    Thursday, February 19, 2009 2:35 PM
  • the clustered index is chosen instead of the non-clustered PK when building the sqlcommands are a known issue. this is a behavior in ADO.NET's getschemaTable() method where the sync adapter builder uses to retrieve the table schema. you might need to manually alter the queries to work around this issue till it is properly addressed in ADO.NET.

    Sorry for the inconvenience.

    thanks
    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Proposed as answer by Yunwen BaiModerator Sunday, February 22, 2009 6:07 AM
    • Marked as answer by G.Stoynev Tuesday, February 24, 2009 2:51 PM
    Saturday, February 21, 2009 4:02 AM
    Moderator

All replies

  • It looks like SqlSyncAdapterBuilder is not using the PK of the table, but seeking *the* clustered unique constraint, which in this case is different than the PK constraint.

    In addition to the PK DDL shown above, the table also has this index:
    CREATE UNIQUE CLUSTERED INDEX [aspnet_Users_Index] ON [dbo].[aspnet_Users] (
    [ApplicationId] ASC,
    [LoweredUserName] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    Other than the obvios incompatibility with the internal SQL Chanhe Tracking (CHANGETABLE returns the correct PK - see post above), this also contradicts the documentation of SqlSyncAdapterBuilder:

    SqlSyncAdapterBuilder.RowGuidColumn Property
    Gets or sets a column of uniqueidentifier data type that is used to identify rows in the base table and the tombstone table.
    Namespace: Microsoft.Synchronization.Data.Server
    Assembly: Microsoft.Synchronization.Data.Server (in microsoft.synchronization.data.server.dll)
    Remarks

    To specify this property, a column that has the same name and the appropriate data type must be present in both the base table and the tombstone table. If this property is not specified, the primary keys of the tables are used to identify rows during synchronization.


    By the way, I tried to specify 
    mySqlSyncAdapterBuilder.RowGuidColumn = "userId";
    but that didn't work - generated SQL was still using [ApplicationId] and [LoweredUserName].

    • Marked as answer by G.Stoynev Tuesday, February 24, 2009 2:50 PM
    Thursday, February 19, 2009 2:35 PM
  • I am referencing 

    C:\Program Files\Microsoft SDKs\Microsoft Sync Framework\v1.0\Runtime\ADO.NET\V2.0\x86\Microsoft.Synchronization.Data.Server.dll, Version: 3.0.0.0
    Thursday, February 19, 2009 2:39 PM
  • In case someone needs this, heres the SQL that I used to discover all tables for which change tracking was enabled, but had non-clustered PK, while having a different CLUSTERED index OR not having a PK at all:

    SELECT T.name,   
        IsChangeTrackingEnabled = CASE WHEN CTT.[object_id] IS NOT NULL THEN 'Yes' ELSE 'No' END,   
        HasPK = CASE WHEN PK.TABLE_NAME IS NULL THEN 'NO' ELSE 'YES' END,  
        HasClusteredIndex = CASE WHEN CI.id IS NULL THEN 'NO' ELSE 'YES' END,  
        PKisCLUSTERED = CASE   
            WHEN    PK.TABLE_NAME IS NOT NULL   
                    AND CI.id IS NOT NULL   
                    AND LOWER(LTRIM(RTRIM(PK.CONSTRAINT_NAME))) <> LOWER(LTRIM(RTRIM(CI.name)))  THEN 'NO' 
            WHEN PK.TABLE_NAME IS NOT NULL AND CI.id IS NOT NULL THEN 'Yes' 
            ELSE 'n/a' 
            END 
    FROM sys.tables T  
        LEFT OUTER JOIN sys.change_tracking_tables CTT ON CTT.[object_id] = T.[object_id]  
        LEFT OUTER JOIN sysindexes CI ON CI.id = T.[object_id]  
                                            AND CI.indid = 1  
        LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON   
                OBJECT_ID(PK.TABLE_SCHEMA + '.' + PK.TABLE_NAME) = T.[object_id]  
                AND PK.CONSTRAINT_TYPE = 'PRIMARY KEY'   
    WHERE T.[type] = 'U' 
        AND (   -- either the table doesn't have a PK  
                PK.TABLE_NAME IS NULL 
                OR -- the table has PK, also has a clustered index but it's not the PK that's clustered  
                (  
                    PK.TABLE_NAME IS NOT NULL   
                    AND CI.id IS NOT NULL   
                    AND LOWER(LTRIM(RTRIM(PK.CONSTRAINT_NAME))) <> LOWER(LTRIM(RTRIM(CI.name)))  
                 )  
            ) 
    Thursday, February 19, 2009 4:13 PM
  • the clustered index is chosen instead of the non-clustered PK when building the sqlcommands are a known issue. this is a behavior in ADO.NET's getschemaTable() method where the sync adapter builder uses to retrieve the table schema. you might need to manually alter the queries to work around this issue till it is properly addressed in ADO.NET.

    Sorry for the inconvenience.

    thanks
    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Proposed as answer by Yunwen BaiModerator Sunday, February 22, 2009 6:07 AM
    • Marked as answer by G.Stoynev Tuesday, February 24, 2009 2:51 PM
    Saturday, February 21, 2009 4:02 AM
    Moderator
  • This explains it, but if SyncAdapterBuilder implementation uses ADO's getschemaTable(), what does SQL Server's CHANGETABLE(CHANGES <table name>, @sync_last_received_anchor) use?

    Why would the implementation of the sync adapter builder use a mechanism incompatible with CHANGETABLE(CHANGES <table name>, @sync_last_received_anchor)?!? The SQL I provide (modified) can be used to extract the "right" index.

    Shouldn't that incompatibility at least be noted in the documentation? This document implies that SyncAdapterBuilder can be used with Change Tracking:
    Getting Started: Client and Server Synchronization

    Tuesday, February 24, 2009 2:50 PM