I have a situation where I'm trying to use filtered syncs. During the initialization process two sync processes are run (FirstPass and SecondPass) of these two sync processes only the SecondPass is filtered. During initialization in the first
pass the core, unfiltered, tables are downloaded these tables include Region, Site, User, and Association. Also, during initialization in the second pass an empty Guid is passed to the filter to download an empty dataset on each of the SecondPass tables.
The SecondPass tables include Building, Floor and Section, etc.
What is happening is that during initialization the FirstPass creates all of the core tables and downloads data and the SecondPass creates all of the secondary tables and downloads no data due to the Guid.Empty that is passed to the filter. This is
exactly how I want things to work.
When I attempt to select a Site for follow-up synchronization I would expect all of the data (Building, Floor, Section, etc.) that satisfy my filter to be downloaded. This, however, is not the case. I end up downloading no data during the follow-up
synchronization.
I've extracted the sql generated from the adapter builder and see that it does indeed return an empty result set. One example of the select incremental inserts sql is below (I've substituted actual values for anchors and client id and posted the @sync_initialized
= 1 section of the query):
SELECT [Building].[ID], [CreatedBy], [CreatedDate], [UpdatedBy], [UpdatedDate], [Name], [SiteID], [Position], [Address1], [Address2], [City], [State], [PostalCode], [ContactName], [ContactPhone], [ContactEmail], [Note] FROM [Building] JOIN CHANGETABLE(CHANGES
[Building], 525) CT ON CT.[ID] = [Building].[ID] WHERE ([Building].SiteID = '95871397-7be8-464e-b756-0054b1603e03') AND (CT.SYS_CHANGE_OPERATION = 'I' AND CT.SYS_CHANGE_CREATION_VERSION <= 580 AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT
<> '70-ED-1B-CE-4F-BD-52-4B-A6-9D-8F-B6-70-35-BD-DF'))
The above query returns an empty resultset.
I took a look at the following select statement and it too returns an empty resultset explaining why the query above returns an empty resultset:
select * from CHANGETABLE(CHANGES [Building], 525) CT
No changes have been made to the data in question for quite sometime so I'm wondering if the change tracking data has been cleaned up and if this is the root cause of my issue.
If this is the root cause of my issue how would one go about getting something like this to work? At the end of the day I need to perform two sync processes... FirstPass (unfiltered) and SecondPass (filtered) and I need to allow users to selectively
sync a particular Site to limit the amount of data they bring down. Additionally, there may be Site and associated data that has sat "dormant" for quite some time and may have had its change tracking data cleaned up.
Any help or suggestions are much appreciated.
Steve E.
