locked
sqlsyncadapterbuilder filter not working on initial sync RRS feed

  • Question

  • I'm using the SqlSyncAdapterBuilder class to generate my adapter.  On the initial synch the GetSchema method fires on my service and produces the following query in Profiler:

    exec sp_executesql N' SET FMTONLY OFF; SET NO_BROWSETABLE ON; SET FMTONLY ON;IF @sync_initialized = 0 SELECT [Site].[SiteId], [ProjectId], [SiteName], [CreatedDate], [ModifiedDate], [CreatedByUserId], [ModifiedByUserId], [IsActive] FROM [Site] ELSE  BEGIN SELECT [Site].[SiteId], [ProjectId], [SiteName], [CreatedDate], [ModifiedDate], [CreatedByUserId], [ModifiedByUserId], [IsActive] FROM [Site] JOIN CHANGETABLE(CHANGES [Site], @sync_last_received_anchor) CT ON CT.[SiteId] = [Site].[SiteId] WHERE (Site.SiteId IN (SELECT Site.SiteId FROM Site JOIN Collector ON Collector.SiteId = Site.SiteId WHERE Collector.CollectorId = @collectorId)) AND (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''[Site]'')) > @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''[Site]'')  END ',N'@sync_initialized bit,@sync_last_received_anchor bigint,@collectorId uniqueidentifier,@sync_new_received_anchor bigint,@sync_client_id_binary varbinary(16)',@sync_initialized=NULL,@sync_last_received_anchor=NULL,@collectorId=NULL,@sync_new_received_anchor=NULL,@sync_client_id_binary=NULL
    This query is running two seperate SQL statements depending on whether @sync_initialized = 0 or not.  You can see that in the SQL it doesn't apply my filter if @sync_initialized = 0.  On the first sync, this means that my filter is bypassed and all data is downloaded to the client.  Is this a bug?  Is there any way to get around it without building the statements myself?
    Friday, January 22, 2010 5:48 PM

Answers

  • Thaks for reporting this issue.  The development team is acknowledged.
    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, January 22, 2010 11:24 PM
    Answerer

All replies

  • Would you mind showing the code to set the filter in the sql adapter builder?

    Thanks.


    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, January 22, 2010 8:12 PM
    Answerer
  • Would you mind showing the code to set the filter in the sql adapter builder?

    Thanks.


    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    private void InitializeSyncAdapters()
    {
    System.Data.SqlClient.SqlConnection connection = this.Connection as System.Data.SqlClient.SqlConnection;
    System.Data.SqlClient.SqlParameter collectorFilter = new System.Data.SqlClient.SqlParameter("@collectorId", System.Data.SqlDbType.UniqueIdentifier);
    this.SyncAdapters.Add(SyncAdapterHelper.GetSyncAdapter(connection, "Site", "Site.SiteId IN (SELECT Site.SiteId FROM Site JOIN Collector ON Collector.SiteId = Site.SiteId WHERE Collector.CollectorId = @collectorId)", collectorFilter));
    } public static SyncAdapter GetSyncAdapter(SqlConnection connection, string tableName, string filter, params System.Data.SqlClient.SqlParameter[] parameters) { SqlSyncAdapterBuilder builder = new SqlSyncAdapterBuilder(connection); builder.TableName = tableName; builder.SyncDirection = Microsoft.Synchronization.Data.SyncDirection.Bidirectional; builder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking; if (String.IsNullOrEmpty(filter) == false) { builder.FilterClause = filter; builder.FilterParameters.AddRange(parameters); } return builder.ToSyncAdapter(); }
    Friday, January 22, 2010 11:23 PM
  • Thaks for reporting this issue.  The development team is acknowledged.
    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, January 22, 2010 11:24 PM
    Answerer
  • I have exactly the same problem, I am new to sync the framework and was investigating filtering last week when I noticed this.

    The online documentation for the sync framework details two aproaches to synchronisation

    1. using the local database cache builder wizard.
    2. Using the SQLSyncAdapterBuilder

    It seems the first doesn't provide for filtering at all and the second doesn't provide proper filtering.

    is it therfore correct to assume that in order to implement filtering: you have to build the statements manualy?

    If so then this should be outlined in the online documentation before more developers spend time trying to figure it all out.

    Tuesday, January 26, 2010 3:10 PM
  • The development team is about to triage it in the next release.

    Thanks.


    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, February 1, 2010 5:26 PM
    Answerer
  • any update from MS or hotfix or any other guidance for this issue : "filtering doesn't work first time, but works in subsequent sync" ?

    Friday, July 9, 2010 10:53 PM
  • When will a solution be available ? Is the fix already released ?
    Wednesday, August 4, 2010 12:39 PM