none
filter rows with sync 2.0 & sql ce RRS feed

  • Question

  •  

    I read here in these forums and else where that there is a known issue with 'filtering rows' - it doesn't work during the first sync operation (initialization), but works in subsequent sync operations. And also read that Microsoft is working on it. Is there an update on when a hotfix/sp will be available?

    We're working with a sizable database (server side) reaching ~4.5GB in size and workign on 'offline' scenario (SQL ce on client side). As SQL has a size restriction of 4.0GB, we can't have it initialized entirely. we want filtering to work first time.

    appreciate any help/recommendations.

    Some more details: using the following

    Sync fx 2.0, sql ce 3.5, sql 2008, vs2008/.net 3.5

    DBServerSyncProvider, SqlCeClientSyncProvider, SyncAgent

    SQL 2008 change tracking.

     

    Thursday, July 22, 2010 9:24 PM

Answers

All replies

  • if you're referring to the Local Database Cache Designer, then yes, you cant filter. But you can always start with an empty database in code, modify the sync generated code to add the filter and   let Sync Fx create the tables.

    check out this similar post: http://jtabadero.spaces.live.com/blog/cns!BF49A449953D0591!1203.entry

    • Marked as answer by mallikn Thursday, July 29, 2010 10:27 PM
    Friday, July 23, 2010 3:05 AM
    Moderator
  • Thanks for your reply. 

    No, I am not using Local Database Cache Designer. I am using Sync Fx 2.0, with sql 2008 change tracking.

    I am basing on this: How To Exchange Bidirectional incremental data: http://msdn.microsoft.com/en-us/library/bb726007.aspx

    I need bidirectional sync, database (sql ce) creation on client, need SQL 2008 change tracking (on server). I am able to get all of these except filtering. 

    Filtering is working, but not the first time (initialization). It works in subsequent syncs. But it was too late by then as too much of data downloaded into client database.

    Can you please point me to how to avoid download of all the data during initialization/creation.

    Appreciate your response.

     

    Friday, July 23, 2010 3:50 AM
  • if you are following the steps from the link you provided above, then you can easily add your filter condition in the SelectIncremental commands and pass the filter value in the configuration parameters before the initial sync.

    can you post the snippet for your SelectIncremental command?

    Friday, July 23, 2010 4:20 AM
    Moderator
  • We're using SQL 2008 built in chane tracking and ahve 60+ tables to sync.  So not handcoding any sql statements (or SPs).

    Below, is the code snippet & sync trace log. Looking into the sync trace file, during the first run, @sync_initialized Value: 0, so the filter is not applied.

    Please let me know if you need more details.

    couple of related posts:

    http://social.microsoft.com/Forums/en-US/syncdevdiscussions/thread/fb29209d-c26c-4913-8753-e353d7de1976

    http://social.microsoft.com/Forums/en-US/uklaunch2007ado.net/thread/9766c28c-00db-4f2b-9416-99678c15f86c

    Thanks,

    ------------ code snippet ---------------------------

       //// //USE CHANGE_TRACKING_CURRENT_VERSION - for anchoring
                SqlCommand selectNewAnchorCommand = new SqlCommand();
                string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
                selectNewAnchorCommand.CommandText =
                    "SELECT " + newAnchorVariable + " = change_tracking_current_version()";
                selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.BigInt);
                selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
                selectNewAnchorCommand.Connection = serverConn;
                this.SelectNewAnchorCommand = selectNewAnchorCommand;

    //add table(s) tao the adapter builder

     SqlSyncAdapterBuilder adapterBuilder = new SqlSyncAdapterBuilder(serverConn);
     adapterBuilder.TableName = table.Schema + table.TableName;
     adapterBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;
                    adapterBuilder.FilterClause = GetFilter(table.TableName);

       SyncAdapter adapter = adapterBuilder.ToSyncAdapter();
       adapter.TableName = table.TableName;
       this.SyncAdapters.Add(adapter);

     

     --------------------- SYNC TRACE -------------------

     Using Command: IF @sync_initialized = 0 SELECT dbo.Sample.[SampleId],<ALL OTHER COLUMNS> FROM dbo.Sample
    ELSE  BEGIN SELECT dbo.Sample.[SampleId], <ALL OTHER COLUMNS> FROM dbo.Sample JOIN CHANGETABLE(CHANGES dbo.Sample, @sync_last_received_anchor) CT ON CT.[SampleId] = dbo.Sample.[SampleId] WHERE (Sample.SampleId in (select SampleId from Sample where Contract_Number = @contractNumber)) 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'dbo.Sample')) > @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'dbo.Sample')  END

     Parameter: @sync_initialized Value: 0
    Parameter: @sync_last_received_anchor Value: 0
    Parameter: @sync_new_received_anchor Value: 624
    Parameter: @sync_client_id_binary Len: 16 Value: 03-9B-91-63-B0-BB-D5-4C-86-97-66-34-FB-9E-80-70

     

     

    Friday, July 23, 2010 4:22 PM
  • you'll have to add your filter to the part where @sync_initialized = 0 as well.

    Saturday, July 24, 2010 3:13 PM
    Moderator
  • June,

    sorry, i didn't understand - how adding @sync_initialized=0 will make the filter work first time. But added it to see how it affects. It didn't change the behavior.

    Looking into the 'sync trace' (below), I think when the framework constructs the select statement to enumerate inserts. It is selecting 'all the rows' (no filter applied) when @sync_initialized=0. I don't know how to inject a filter into the 'IF' part, the filter is injected properly into the 'ELSE' part.

    Appreciate your help.

    copied below the filter statement (as you suggested) and the 'sync trace' below :

    ------------- CODE SNIPPET: FILTER STATEMENT --------------------

    string GetFilter(string tableName)
            {
                return string.Format(@"@sync_initialized = 0 and {0}.SampleId in (select SampleId from Sample where Contract_Number = @contractNumber)", tableName);
               
            }

    -------------------------- SYNC TRACE ---------------------------

    ----- Enumerating Inserts for Table Sample -----

    Using Command:

    IF @sync_initialized = 0
     SELECT dbo.Sample.[SampleId], <ALL OTHER COLUMNS>
      FROM dbo.Sample
    ELSE 
    BEGIN
        SELECT dbo.Sample.[SampleId], <ALL OTHER COLUMNS>
        FROM dbo.Sample
        JOIN CHANGETABLE(CHANGES dbo.Sample, @sync_last_received_anchor) CT ON CT.[SampleId] = dbo.Sample.[SampleId]
     WHERE (@sync_initialized = 0 and Sample.SampleId in (select SampleId from Sample where Contract_Number = @contractNumber)) 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));

    Parameter: @sync_initialized Value: 0
    Parameter: @sync_last_received_anchor Value: 0
    Parameter: @sync_new_received_anchor Value: 659
    Parameter: @sync_client_id_binary Len: 16 Value: 93-65-6D-26-F9-30-88-46-89-62-B5-56-33-A3-59-CB

    Monday, July 26, 2010 8:44 PM
  • i meant you should add your WHERE clause in the IF @sync_inialized = 0 block as well

    IF @sync_initialized = 0
     SELECT dbo.Sample.[SampleId], <ALL OTHER COLUMNS>
      FROM dbo.Sample WHERE (Sample.SampleId in (select SampleId from Sample where Contract_Number = @contractNumber))
    ELSE 
    BEGIN
        SELECT dbo.Sample.[SampleId], <ALL OTHER COLUMNS>
        FROM dbo.Sample
        JOIN CHANGETABLE(CHANGES dbo.Sample, @sync_last_received_anchor) CT ON CT.[SampleId] = dbo.Sample.[SampleId]
     WHERE (Sample.SampleId in (select SampleId from Sample where Contract_Number = @contractNumber)) 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));

    Monday, July 26, 2010 10:18 PM
    Moderator
  • ok. got it.

    But i am not hand coding any sqls (inserts, updates, etc). Sync FX is generating these SQLs for me.

    I am injecting only the 'filter' through 'adapterBuilder.FilterClause'.

    As I've too many tables (~65 now and may grow) and If I handcode these SQLs, then it'll be hard for the maintenance (changes in schemas etc). So I am relying on Framework to generate the SQLs.

    Everything is working fine, but don't know how to inject a 'filter' into the 'IF" block (when @sync_initiailzed=0).

    Hope my question is clear.

    Monday, July 26, 2010 10:25 PM
  • may be I'll re-state it.

    ------ begin code --------------

       string filter = GetFilter(table.TableName);
    adapterBuilder.FilterClause = filter;

            string GetFilter(string tableName)
            {
                return string.Format(@"@sync_initialized = 0 and {0}.SampleId in (select SampleId from Sample where Contract_Number = @contractNumber)", tableName);
               
            }

    ---------------end code -----------

    That is all i am doing, and sync fx is generating those SQLs (those i copied earlier - from sync trace). it is injecting the filter properly in the 'else' block but not in the 'if' block (when @sync_initialized=0). i can't figure out - how to force the sync Fx to add the filter in the 'if' block.

     

    Monday, July 26, 2010 11:09 PM
  • just for the record. i've programtically altered the sqls for incrementalInserts & incrementalUpdates. and solves my filtering issue.

    will accept the reply (above) from June as answer.

    my original question still remains :) - is it a bug that sync framework is not injecting the filter clause into the 'if' block (when @sync_initialized=0). if not a bug, then what'd be the reason for not injecting the filter cluase? hopefully i shall find out later. for now this sync work (offline) is done and moving on to other stuff (batching, n-tier etc)

    June, thanks for patiently answering.

    Thursday, July 29, 2010 10:27 PM
  • This is clearly a bug. Very frustrating. Can you post more details regarding your solution?
    Saturday, August 14, 2010 5:19 PM
  • i've followed this article: http://jtabadero.spaces.live.com/blog/cns!BF49A449953D0591!1203.entry?wa=wsignin1.0&sa=388472747

    basically i had to set the adapter.SeletIncrementalInsertCommand in the code, for filter to work.

    HTH!

     

    Friday, August 20, 2010 5:22 PM