Filtering MS Sync Framework

Question

hello been said filtering works with latest Microsoft Sync Framework version. so I used filter passing from client to server

on server(sqlServer08 with change tracking enabled) I have

SqlParameter filterParameter = new SqlParameter("@Institution_ID", SqlDbType.UniqueIdentifier);

    string customerFilterClause = "Institution_ID=@Institution_ID";
    customerBuilder.FilterClause = customerFilterClause;
    customerBuilder.FilterParameters.Add(filterParameter);

and on client I have

SyncTable depsSyncTable = new SyncTable("department"); depsSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable; depsSyncTable.SyncDirection = SyncDirection.DownloadOnly;

        this.Configuration.SyncTables.Add(depsSyncTable);
        this.Configuration.SyncParameters.Add(
             new SyncParameter("@Institution_ID", new Guid("248a1343-decb-45a5-906f-2fa4d17f8d76")));

but filtering doesnt work as it expected, it getms me all data not filteres by institution ID. what I found also that it goes like that only on first time I do syncronize, so while I manually add new rows and press again sync it works great on client and filters them. To summarize I understand that first time Sync creates some anchor and uses it as base point so all prev data gets loaded onto the client, but I would say this is wrong as I still would like my filter working right.

can you help me here? thanks

Answer 1

It looks like you want to filter the data that is being synchronized from the server to the client - only data for a specific institution should be downloaded to the client.

The problem here might be that you are providing two parameters - one without a value. Consider removing the SyncParameter and setting the value for the SqlParameter that you pass to the builder like this.

SqlParameter filterParameter = new SqlParameter("@Institution_ID", SqlDbType.UniqueIdentifier);
filterParameter.Value = new Guid("248a1343-decb-45a5-906f-2fa4d17f8d76");

An alternative (one that I have not tried) might be to remove the Sql Parameter - I would leave the filter clause as you have it.

Answer 2

Actually I did that right, but problem is that it doesnt save CLIENT_ID and gets all the data, since doesn't know changed files... I solved this problem another way.

Answer 3

I just hit this exact problem. It downloads the entire data first time and from there on it filters as expected. But was too late by then, unwanted data already downloaded.

Care to share how you solved it?

When going through the sync-trace, this select statement. (replaced original 'table name 's and 'columns/keys') has '@sync_initialized=0' *first time* and in subsequent sync it is substituted as '1' so it is goign into 'else' block and hence filtering is working.

Below is the excerpt from Trace file:

---

VERBOSE, , 1, 07/09/2010 15:54:32:408, Using Command: "IF @sync_initialized = 0 SELECT FROM ELSE BEGIN SELECT FROM JOIN CHANGETABLE(CHANGES , @sync_last_received_anchor) CT ON CT.[keyId] = .[keyId] WHERE (.[keyId[ in (select from where = @parm1)) 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'')) > @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'') END "

---------- parm values that are substituted in teh above query ----------- VERBOSE, , 1, 07/09/2010 15:54:32:408, Parameter: @sync_initialized Value: 0 VERBOSE, , 1, 07/09/2010 15:54:32:408, Parameter: @sync_last_received_anchor Value: 0 VERBOSE, , 1, 07/09/2010 15:54:32:408, Parameter: @sync_new_received_anchor Value: 156 VERBOSE, , 1, 07/09/2010 15:54:32:408, Parameter: @sync_client_id_binary Len: 16 Value: CE-5F-CB-9F-43-6E-71-4D-BE-5C-3C-9A-3C-CA-0A-26