none
Provisioning bug Sync Framework 2.1 RRS feed

  • Question

  • Hi All,
    I am working with Sync Framework and I have the following problem:
    I have two tables for example:
    • Table1
      • Id (int identity(1,1))
      • Name (nvarchar(50))
      • Active (bit)
    • Table2
      • Id (uniqueidentifier)
      • Name (nvarchar(50))
      • Active (bit)

    My configuration to provision:
    • Scope1
      • Scope without filter
      • Table1 with all columns
      • Table2 with all columns
    • Scope2
      • A template to use filtering
      • Table1
        • Filterclause: [side].[Active] = @Table1_Active
        • Filtercolumns
          • Active
        • Filter parameters
          • @Table1_Active bit
      • Table2
        • Filtercolums
        • Filterclause: [side].[Active] = @Table2_Active
          • Active
        • Filter parameters
          • @Table2_Active bit
    I know you cannot have overlapping scopes but it exactly reproduce my problem.
    If you first provision Scope2 and then Scope1 everything is correct becuase the following steps are correct:
    • During provisioning Scope2
      • Create tracking tables with an additional filter column (Active)
      • Create triggers for both tables which are aware of the Active column in the tracking tables
    • During provisioning Scope1
      • Don't create tracking tables again (serverConfig.SetCreateTrackingTableDefault(DbSyncCreationOption.CreateOrUseExisting))
      • Don't alter or create triggers (serverConfig.SetCreateTriggerDefault(DbSyncCreationOption.CreateOrUseExisting))

    Everything will be OK. If you update a record and change the value in the Active column in table1, the table1_update_trigger will also change the value in the Active column of table1_tracking table.
    Now the problem situation:
    If you first provision Scope1 and then Scope2, the insert, update and delete triggers are not aware of the Active column in the tracking tables:
    The following steps are performed:
    • During provisioning Scope1
      • Create tracking tables without active column (because Scope1 does not have any filters and filter columns)
      • Create triggers for both tables (these triggers are not aware of the Active filter column because Scope1 does not contain a filter)
    • During provisioning Scope2
      • ALTER tracking tables and add Active column. (serverConfig.SetCreateTrackingTableDefault(DbSyncCreationOption.CreateOrUseExisting))
      • ALTER triggers but they are still not aware of the Active columns (serverConfig.SetCreateTriggerDefault(DbSyncCreationOption.CreateOrUseExisting))

    In other situations the ALTER TRIGGER query will update the trigger to make it aware of the Active column.
    Is there a workaround for this?
    BUG INFORMATION: 
    The bug is probably in
    SqlSyncTableProvisioning.AddNewFilterColumnsToTrackingTable(SqlTransaction trans)
    
    This method contains the following code:
    if (flag) // flag = true if a new column is added to the tracking table
    {
      // This method is causing problems becuase it is looking at the current tracking table and is not adding the new columns
      this._triggerHelper.FilterColumns = this.GetExistingFilterColumns(trans);
    
      // These statements don't know the filtercolumns because of the wrong GetExistingFilterColumns method.
      this._triggerHelper.AlterInsertTrigger(trans);
      this._triggerHelper.AlterUpdateTrigger(trans);
      this._triggerHelper.AlterDeleteTrigger(trans);
    }
    
    Another question:
    Is Sync Framework Team aware of this bug?
    Is there a way to report this bug?
     
    Thanks in advance for your answer



    Tuesday, June 7, 2011 11:57 AM

Answers

  • actually, by the time GetExistingFilterColumns gets called, the new filter column has been added and populated already in the tracking table and GetExistingFilterColumns actually reads the column information from the INFORMATION.SCHEMA.COLUMNS.

    are you scripting the provisioning? if you are, it's a known bug.

    because the provisioning is just scripted, the GetExistingFilterColumns would not detect the newly added filter column since nothing has been added to the table yet.

    the second issue on the selectchanges being created on the dbo schema i think is a known bug.


    • Marked as answer by HWC Verweij Thursday, June 16, 2011 7:19 AM
    Thursday, June 16, 2011 2:16 AM
    Moderator

All replies

  • Hi,

    Are you using dbo schema.

     

    Thanks,
    Ramesh V

    Tuesday, June 14, 2011 6:30 PM
  • Hi Ramesh V,

    Thanks for your reply.

    Sync Framework is using its own schema.
    These are the schemas:
    - Domain Model:     dbo (for example dbo.Users)
    - Sync Framework: sync (for example sync.Users_tracking)

    Wednesday, June 15, 2011 7:12 AM
  • can you enable Sync Fx tracing and paste the trace here?

    Wednesday, June 15, 2011 8:04 AM
    Moderator
  • Hi JuneT
    The log is to big if I enable VERBOSE as loglevel. So I insert here the logfile on level INFO.
    I have added some extra information from the verbose logfile on the loglines about creating triggers:
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:48:695, Version of connection detected to be Sql2008
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:48:711, ----- Populating Scope Provisioning from Scope Description for 'UploadScope' -----
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:48:711, Adding Table Provisioning for '[Regions]'
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:48:742, --- END Populating Scope Provisioning from Scope Description for 'UploadScope' ---
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:48:773, ----- Scripting Creation of Scope 'UploadScope' on Database 'ServerFilteringTest' -----
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:48:773, Enable Snapshot Isolation on Database 'ServerFilteringTest' if needed
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:48:773, Create Scope Info Table named [sync].[scope_info]
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:48:773, Create Scope Config Table named [sync].[scope_config]
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:48:773, Create Schema Info Table named [sync].[schema_info]
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:48:773, Add version info for schema
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:48:789, Creating procedure '[sync].[Regions_selectchanges]' as it does not exist
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:163, Add scope configuration entry for Config ID 'ee9fec0d-9640-4400-acf7-955b00e702a2'
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:163, Add Scope Info entry for Scope 'UploadScope'
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:163, ----- Scripting Provisioning of Table '[Regions]' -----
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:163, Create Table Option: Skip
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:179, Create Tracking Table Option: CreateOrUseExisting
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:179, Creating Tracking Table '[sync].[Regions_tracking]' as it does not exist
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:179, Create Tracking Table [sync].[Regions_tracking]
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:179, Create Primary Key on Tracking Table [sync].[Regions_tracking]
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:179, Create index on Tracking Table [sync].[Regions_tracking]
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:179, Create Trigger Option: CreateOrUseExisting
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:179, Creating Trigger '[Regions_insert_trigger]' as it does not exist
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:179, Insert Trigger for table [Regions]
    VERBOSE, WebDev.WebServer40, 7, 06/15/2011 09:33:35:198,    CREATE TRIGGER [Regions_insert_trigger] ON [Regions] FOR INSERT AS
    UPDATE [side] SET [sync_row_is_tombstone] = 0, [local_update_peer_key] = 0, [restore_timestamp] = NULL, [update_scope_local_id] = NULL, [last_change_datetime] = GETDATE() FROM [sync].[Regions_tracking] [side] JOIN INSERTED AS [i] ON [side].[Id] = [i].[Id]
    INSERT INTO [sync].[Regions_tracking] ([i].[Id], [create_scope_local_id], [local_create_peer_key], [local_create_peer_timestamp], [update_scope_local_id], [local_update_peer_key], [sync_row_is_tombstone], [last_change_datetime], [restore_timestamp]) SELECT [i].[Id], NULL, 0, @@DBTS+1, NULL, 0, 0, GETDATE() , NULL FROM INSERTED AS [i] LEFT JOIN [sync].[Regions_tracking] [side] ON [side].[Id] = [i].[Id] WHERE [side].[Id] IS NULL
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:179, Creating Trigger '[Regions_update_trigger]' as it does not exist INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:194, Update Trigger for table [Regions] VERBOSE, WebDev.WebServer40, 7, 06/15/2011 09:33:35:198,    CREATE TRIGGER [Regions_update_trigger] ON [Regions] FOR UPDATE AS
    UPDATE [side] SET [local_update_peer_key] = 0, [restore_timestamp] = NULL, [update_scope_local_id] = NULL, [last_change_datetime] = GETDATE() FROM [sync].[Regions_tracking] [side] JOIN INSERTED AS [i] ON [side].[Id] = [i].[Id]
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:194, Creating Trigger '[Regions_delete_trigger]' as it does not exist INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:194, Alter Delete Trigger for table [Regions] VERBOSE, WebDev.WebServer40, 7, 06/15/2011 09:33:35:198,    CREATE TRIGGER [Regions_delete_trigger] ON [Regions] FOR DELETE AS
    UPDATE [side] SET [sync_row_is_tombstone] = 1, [local_update_peer_key] = 0, [restore_timestamp] = NULL, [update_scope_local_id] = NULL, [last_change_datetime] = GETDATE() FROM [sync].[Regions_tracking] [side] JOIN DELETED AS [d] ON [side].[Id] = [d].[Id]
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:194, Create Procedures Option: CreateOrUseExisting INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:194, Create Additional Procedures Option: Create INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:194, Creating procedure '[sync].[Regions_selectchanges]' as it does not exist VERBOSE, WebDev.WebServer40, 7, 06/15/2011 09:33:35:229,    CREATE PROCEDURE [Regions_selectchanges]
     @sync_min_timestamp BigInt,
     @sync_scope_local_id Int,
     @sync_scope_restore_count Int,
     @sync_update_peer_key Int
    AS
    BEGIN
    SELECT [side].[Id], [base].[Name], [base].[Position], [base].[Active], [base].[RegionType_Id], [side].[sync_row_is_tombstone], [side].[local_update_peer_timestamp] as sync_row_timestamp, case when ([side].[update_scope_local_id] is null or [side].[update_scope_local_id] <> @sync_scope_local_id) then COALESCE([side].[restore_timestamp], [side].[local_update_peer_timestamp]) else [side].[scope_update_peer_timestamp] end as sync_update_peer_timestamp, case when ([side].[update_scope_local_id] is null or [side].[update_scope_local_id] <> @sync_scope_local_id) then case when ([side].[local_update_peer_key] > @sync_scope_restore_count) then @sync_scope_restore_count else [side].[local_update_peer_key] end else [side].[scope_update_peer_key] end as sync_update_peer_key, case when ([side].[create_scope_local_id] is null or [side].[create_scope_local_id] <> @sync_scope_local_id) then [side].[local_create_peer_timestamp] else [side].[scope_create_peer_timestamp] end as sync_create_peer_timestamp, case when ([side].[create_scope_local_id] is null or [side].[create_scope_local_id] <> @sync_scope_local_id) then case when ([side].[local_create_peer_key] > @sync_scope_restore_count) then @sync_scope_restore_count else [side].[local_create_peer_key] end else [side].[scope_create_peer_key] end as sync_create_peer_key FROM [Regions] [base] RIGHT JOIN [sync].[Regions_tracking] [side] ON [base].[Id] = [side].[Id] WHERE  ([side].[update_scope_local_id] IS NULL OR [side].[update_scope_local_id] <> @sync_scope_local_id OR ([side].[update_scope_local_id] = @sync_scope_local_id AND [side].[scope_update_peer_key] <> @sync_update_peer_key)) AND [side].[local_update_peer_timestamp] > @sync_min_timestamp
    END
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:194, Select Incremental Changes command for table [Regions] INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:194, Creating procedure '[sync].[Regions_selectrow]' as it does not exist INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:210, Select Row command for table [Regions] INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:210, Creating procedure '[sync].[Regions_insert]' as it does not exist INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:210, Insert Command for table [Regions] INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:210, Creating procedure '[sync].[Regions_update]' as it does not exist INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:210, Update Command for table [Regions] INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:210, Creating procedure '[sync].[Regions_delete]' as it does not exist INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:210, Delete Command for table [Regions] INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:210, Creating procedure '[sync].[Regions_insertmetadata]' as it does not exist INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:210, Insert Metadata Command for table [Regions] INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:225, Creating procedure '[sync].[Regions_updatemetadata]' as it does not exist INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:225, Update Metadata Command for table [Regions] INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:225, Creating procedure '[sync].[Regions_deletemetadata]' as it does not exist INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:225, Delete Metadata Command for table [Regions] INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:225, Creating table type '[sync].[Regions_BulkType]' as it does not exist INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:225, Create TVP Type on table [Regions] INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:225, Creating procedure '[sync].[Regions_bulkinsert]' as it does not exist INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:225, Bulk Insert Command for table [Regions] INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:225, Creating procedure '[sync].[Regions_bulkupdate]' as it does not exist INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:225, Bulk Update Command for table [Regions] INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:241, Creating procedure '[sync].[Regions_bulkdelete]' as it does not exist INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:241, Bulk Delete Command for table [Regions] INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:241, Populating tracking table from base table INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:241, Populate tracking table [sync].[Regions_tracking] for existing data in table [Regions] INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:241, --- END Scripting Provisioning of Table '[Regions]' --- INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:272, --- END Scripting Creation of Scope 'UploadScope' on Database 'ServerFilteringTest' --- INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:338, Version of connection detected to be Sql2008 INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:353, ----- Populating Template Provisioning from Scope Description for 'DownloadScope_template' ----- INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:353, Adding Table Provisioning for '[Regions]' INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:384, --- END Populating Template Provisioning from Scope Description for 'DownloadScope_template' --- INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:416, ----- Scripting Creation of Template 'DownloadScope_template' on Database 'ServerFilteringTest' ----- INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:416, Enable Snapshot Isolation on Database 'ServerFilteringTest' if needed INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:416, Create Scope Info Table named [sync].[scope_info] INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:416, Create Scope Config Table named [sync].[scope_config] INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:416, Create Scope Config Table named [sync].[scope_templates] INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:416, Create Scope Config Table named [sync].[scope_parameters] INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:416, Create Schema Info Table named [sync].[schema_info] INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:416, Add version info for schema INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:416, Creating procedure '[sync].[Regions_selectchanges]' as it does not exist INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:416, Add scope configuration entry for Config ID 'e59d3bb9-4de7-4cec-9054-84ed3591cc65' INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:416, Add Scope Template entry for Template named 'DownloadScope_template' INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:431, ----- Scripting Provisioning of Table '[Regions]' ----- INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:431, Create Table Option: Skip INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:431, Create Tracking Table Option: CreateOrUseExisting INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:431, Skipping creation of Tracking Table '[sync].[Regions_tracking]' since it already exists INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:431, Filter column '[Active]' needs to be added to triggers and side table INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:431, Add new filter column, Active, to Tracking Table [sync].[Regions_tracking] VERBOSE, WebDev.WebServer40, 7, 06/15/2011 09:34:02:089,    ALTER TABLE [sync].[Regions_tracking] ADD [Active] bit NULL
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:447, Populate new filter column [Active] on Tracking Table [sync].[Regions_tracking] VERBOSE, WebDev.WebServer40, 7, 06/15/2011 09:34:02:089,    UPDATE [side] SET [Active] = [base].[Active], [restore_timestamp] = COALESCE([restore_timestamp], [local_update_peer_timestamp]) FROM [sync].[Regions_tracking] [side] JOIN [Regions] [base] ON [side].[Id] = [base].[Id]
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:447, Alter Insert Trigger for table [Regions]
    VERBOSE, WebDev.WebServer40, 7, 06/15/2011 09:34:02:105,    ALTER TRIGGER [Regions_insert_trigger] ON [Regions] FOR INSERT AS
    UPDATE [side] SET [sync_row_is_tombstone] = 0, [local_update_peer_key] = 0, [restore_timestamp] = NULL, [update_scope_local_id] = NULL, [last_change_datetime] = GETDATE() FROM [sync].[Regions_tracking] [side] JOIN INSERTED AS [i] ON [side].[Id] = [i].[Id]
    INSERT INTO [sync].[Regions_tracking] ([i].[Id], [create_scope_local_id], [local_create_peer_key], [local_create_peer_timestamp], [update_scope_local_id], [local_update_peer_key], [sync_row_is_tombstone], [last_change_datetime], [restore_timestamp]) SELECT [i].[Id], NULL, 0, @@DBTS+1, NULL, 0, 0, GETDATE() , NULL FROM INSERTED AS [i] LEFT JOIN [sync].[Regions_tracking] [side] ON [side].[Id] = [i].[Id] WHERE [side].[Id] IS NULL INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:447, Alter Update Trigger for table [Regions] VERBOSE, WebDev.WebServer40, 7, 06/15/2011 09:34:02:105,    ALTER TRIGGER [Regions_update_trigger] ON [Regions] FOR UPDATE AS
    UPDATE [side] SET [local_update_peer_key] = 0, [restore_timestamp] = NULL, [update_scope_local_id] = NULL, [last_change_datetime] = GETDATE() FROM [sync].[Regions_tracking] [side] JOIN INSERTED AS [i] ON [side].[Id] = [i].[Id]
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:447, Alter Delete Trigger for table [Regions] VERBOSE, WebDev.WebServer40, 7, 06/15/2011 09:34:02:105,    ALTER TRIGGER [Regions_delete_trigger] ON [Regions] FOR DELETE AS
    UPDATE [side] SET [sync_row_is_tombstone] = 1, [local_update_peer_key] = 0, [restore_timestamp] = NULL, [update_scope_local_id] = NULL, [last_change_datetime] = GETDATE() FROM [sync].[Regions_tracking] [side] JOIN DELETED AS [d] ON [side].[Id] = [d].[Id]
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:447, Create Trigger Option: CreateOrUseExisting INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:447, Skipping creation of Trigger '[Regions_insert_trigger]' since it already exists INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:447, Skipping creation of Trigger '[Regions_update_trigger]' since it already exists INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:447, Skipping creation of Trigger '[Regions_delete_trigger]' since it already exists INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:447, Create Procedures Option: CreateOrUseExisting INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:447, Create Additional Procedures Option: Create INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:447, Creating procedure '[sync].[Regions_selectchanges]' as it does not exist INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:447, Select Incremental Changes command for table [Regions] INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:447, Creating procedure '[sync].[Regions_selectrow]' as it does not exist INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:447, Select Row command for table [Regions] INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:447, Creating procedure '[sync].[Regions_insert]' as it does not exist INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:447, Insert Command for table [Regions] INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:447, Creating procedure '[sync].[Regions_update]' as it does not exist INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:462, Update Command for table [Regions] INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:462, Creating procedure '[sync].[Regions_delete]' as it does not exist INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:462, Delete Command for table [Regions] INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:462, Creating procedure '[sync].[Regions_insertmetadata]' as it does not exist INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:462, Insert Metadata Command for table [Regions] INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:462, Creating procedure '[sync].[Regions_updatemetadata]' as it does not exist INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:462, Update Metadata Command for table [Regions] INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:462, Creating procedure '[sync].[Regions_deletemetadata]' as it does not exist INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:462, Delete Metadata Command for table [Regions] INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:462, Creating table type '[sync].[Regions_BulkType]' as it does not exist INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:462, Create TVP Type on table [Regions] INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:462, Creating procedure '[sync].[Regions_bulkinsert]' as it does not exist INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:462, Bulk Insert Command for table [Regions] INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:462, Creating procedure '[sync].[Regions_bulkupdate]' as it does not exist INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:462, Bulk Update Command for table [Regions] INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:462, Creating procedure '[sync].[Regions_bulkdelete]' as it does not exist INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:462, Bulk Delete Command for table [Regions] INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:462, --- END Scripting Provisioning of Table '[Regions]' --- INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:57:525, --- END Scripting Creation of Template 'DownloadScope_template' on Database 'ServerFilteringTest' ---
    As you can see the Sync Framework detects the new column, add this new column to the tracking table.
    Sync Framework also populate this new column in the tracking table with data from the dbo.Regions table.
    It also change the current triggers but the content of the triggers is incorrect. It does not update, insert or delete the new filtercolumn.
    If you look into the source of Sync Framework Microsoft.Synchronization.Data.SqlServer.SqlSyncTableProvisioning.AddNewFilterColumnsToTrackingTable(SqlTransaction trans) you will see it is only taking the current columns of the tracking table and it totally forget the new filter column.
    if (flag) // flag = true if a new column is added to the tracking table
    {
     // This method is causing problems becuase it is looking at the current tracking table and is not adding the new columns
     this._triggerHelper.FilterColumns = this.GetExistingFilterColumns(trans);
    
     // These statements don't know the filtercolumns because of the wrong GetExistingFilterColumns method.
     this._triggerHelper.AlterInsertTrigger(trans);
     this._triggerHelper.AlterUpdateTrigger(trans);
     this._triggerHelper.AlterDeleteTrigger(trans);
    }
    
    
    This logfile also shows a second bug:
    INFO , WebDev.WebServer40, 7, 06/15/2011 09:52:49:194, Creating procedure '[sync].[Regions_selectchanges]' as it does not exist
    VERBOSE, WebDev.WebServer40, 7, 06/15/2011 09:33:35:229, CREATE PROCEDURE [Regions_selectchanges]
     @sync_min_timestamp BigInt,
     @sync_scope_local_id Int,
     @sync_scope_restore_count Int,
     @sync_update_peer_key Int
    AS
    BEGIN
    SELECT [side].[Id], [base].[Name], [base].[Position], [base].[Active], [base].[RegionType_Id], [side].[sync_row_is_tombstone], [side].[local_update_peer_timestamp] as sync_row_timestamp, case when ([side].[update_scope_local_id] is null or [side].[update_scope_local_id] <> @sync_scope_local_id) then COALESCE([side].[restore_timestamp], [side].[local_update_peer_timestamp]) else [side].[scope_update_peer_timestamp] end as sync_update_peer_timestamp, case when ([side].[update_scope_local_id] is null or [side].[update_scope_local_id] <> @sync_scope_local_id) then case when ([side].[local_update_peer_key] > @sync_scope_restore_count) then @sync_scope_restore_count else [side].[local_update_peer_key] end else [side].[scope_update_peer_key] end as sync_update_peer_key, case when ([side].[create_scope_local_id] is null or [side].[create_scope_local_id] <> @sync_scope_local_id) then [side].[local_create_peer_timestamp] else [side].[scope_create_peer_timestamp] end as sync_create_peer_timestamp, case when ([side].[create_scope_local_id] is null or [side].[create_scope_local_id] <> @sync_scope_local_id) then case when ([side].[local_create_peer_key] > @sync_scope_restore_count) then @sync_scope_restore_count else [side].[local_create_peer_key] end else [side].[scope_create_peer_key] end as sync_create_peer_key FROM [Regions] [base] RIGHT JOIN [sync].[Regions_tracking] [side] ON [base].[Id] = [side].[Id] WHERE ([side].[update_scope_local_id] IS NULL OR [side].[update_scope_local_id] <> @sync_scope_local_id OR ([side].[update_scope_local_id] = @sync_scope_local_id AND [side].[scope_update_peer_key] <> @sync_update_peer_key)) AND [side].[local_update_peer_timestamp] > @sync_min_timestamp
    END
    
    
    This two logmessages shows that the select changes stored procedure supose to be created in the 'sync' schema (first logmessage INFO....) but it isn't. The query (second logmessage VERBOSE....) shows that the stored procedure is created in the 'dbo' schema.
    For the last bug I have already implemented a workaround but for the first bug I don't want to recreate the triggers by myself.
    So I hope this bug is a known issue by the Sync Framework Team or you hopefully can help me to workaround this problem.

    Thanks in advance.
    Wednesday, June 15, 2011 12:25 PM
  • actually, by the time GetExistingFilterColumns gets called, the new filter column has been added and populated already in the tracking table and GetExistingFilterColumns actually reads the column information from the INFORMATION.SCHEMA.COLUMNS.

    are you scripting the provisioning? if you are, it's a known bug.

    because the provisioning is just scripted, the GetExistingFilterColumns would not detect the newly added filter column since nothing has been added to the table yet.

    the second issue on the selectchanges being created on the dbo schema i think is a known bug.


    • Marked as answer by HWC Verweij Thursday, June 16, 2011 7:19 AM
    Thursday, June 16, 2011 2:16 AM
    Moderator
  • Hi JuneT,

     

    Thanks for your answer.

    Yes I am scripting the provisioning because I have written some code around Sync Framework for more flexibility in using scopes.

    These additional code use some tables which need to be updated in the same transaction.

    This is not posible without scripting.

     

    So what is recommended to do?

    It is no option to use SqlSyncScopeProvisioning.Apply() instaed of SqlSyncScopeProvisioning.Script()

    It is also no option to change te script manually because the whole configuration process in automated.

     

    This is my solution I have quickly implemented:

    The workaround I have implemented is to create my own triggers if I am in the 'bug situation'.

    But is Sync Framework Team release Sync Fx 4.0 I hopefully can throw away this code. 

     

    I am still wondering if there is a buglist available to look into. (To know which one will be solved in next release and to know which one aren't reported yet)

    And: Is there a place to report bugs or do I need to ask a question on this forum?

     

    Thanks all for help

     

     

    Thursday, June 16, 2011 7:19 AM
  • you can try scripting still but using SetCreateTriggerDefault to Create, then just do string processing on the script replacing CREATE TRIGGER with ALTER TRIGGER much like the approach here: http://jtabadero.wordpress.com/2011/04/13/modifying-sync-framework-scope-definition-part-3-workarounds-addingremoving-columns/

    i doubt this will be fixed in the v4 release (postponed) since its mostly around support of Silverlight, WP7, etc.. and  runs on top of Sync Fx 2.1.

    Im not aware of any central bug list for Sync Framework as well (cant recall if they have a Connect site), so posting it here is probably the best way to report it.

     

    Thursday, June 16, 2011 1:58 PM
    Moderator
  • Hi June,

     

    This example is not proper working in some situation.

    If you create a filtered scope/template which contains table T1 and filters on column C1.
    If you now create a normal scope for T1, you have the same problem.

     

    Also in case of another filtered scope/template which filters on column C2 will cause problems.
    The new trigger will only update T1_tracking.C2 and not T1_tracking.C1.

     

    So I have written some code to create the triggers by myself to replace the 'buggy' triggers.

    Monday, June 20, 2011 11:49 AM
  • actually, if you provision the scope with the script with the "buggy" alter trigger, sync fx would pick up the new filter columns already.
    Tuesday, June 21, 2011 12:48 AM
    Moderator