locked
Filter database server data RRS feed

  • Question

  • Hello, i'm using CTP 2. I need help to sync filtered data from database server to client database (sql server 2008). I need filter tables for a user id field but i don't know how to do.
    If someone has a simple example so I will thank.

    Tuesday, August 11, 2009 12:20 PM

Answers

  • You've just hit my biggest pain pain point with filtering. This is not currenly possible with the latest bits as far as I know. The recommendation is to design your scopes based on static filter keys.

    Sync Team, if this has changed or there are any plans to add the support for dynamic filter parameters, please please advise.

    Thanks,

    Rick
    Rick
    • Marked as answer by EricDelahaye Wednesday, August 12, 2009 6:05 PM
    Wednesday, August 12, 2009 4:35 PM
  • Yes!
    I need to put allow snapshot isolation on the other database. Thank you Sql Profiler!!!

    ALLOW_SNAPSHOT_ISOLATION

     

    ON

    • Marked as answer by EricDelahaye Thursday, August 27, 2009 6:59 PM
    Thursday, August 27, 2009 6:59 PM

All replies

  • Eric,

    When you provision your table(s) within a scope, you specify a filter and filter key.

    Per this walkthrough: http://msdn.microsoft.com/en-us/library/dd918848(SQL.105).aspx here is the boilerplate code you use to accomplish this:

    SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(scopeDesc);
    serverConfig.CreateTableDefault = DbSyncCreationOption.Skip;
    
    // Specify which column(s) in the Customer table to use for filtering data, 
    // and the filtering clause to use against the tracking table.
    // "[side]" is an alias for the tracking table.
    serverConfig["Customer"].AddFilterColumn("CustomerType");
    serverConfig["Customer"].FilterClause = "[side].[CustomerType] = 'Retail'";
    
    // Configure the scope and change tracking infrastructure.
    serverConfig.Apply(serverConn);
    
    // Write the configuration script to a file. You can modify 
    // this script if necessary and run it against the server
    // to customize behavior.
    File.WriteAllText("SampleConfigScript.txt", 
        serverConfig.Script("SyncSamplesDb_SqlPeer1"));

    And here is a helper method I've written to simplify the provisioning process:

    /// <summary>
            /// Used to generate provisioning scripts for a given scope.
            /// </summary>
            /// <param name="scopeName">The name of the scope to be provisioned.</param>
            /// <param name="tableName">The name of the table that corresponds to the scope.</param>
            /// <param name="filterColumn">The name of the column that will be used for filtering.</param>
            /// <param name="filterKey">The value that the filter column shoud use to filter.</param>
            public void ProvisionScope(string scopeName, string tableName, string filterColumn, string filterKey)
            {
    
                /* Describe the scope and tables */
    
                // Define scope
                DbSyncScopeDescription serverScopeDescription = new DbSyncScopeDescription(scopeName);
    
                // Get metadata of source replica table
    
                // Table
                DbSyncTableDescription table = SqlSyncDescriptionBuilder.GetDescriptionForTable(tableName, m_ServerConnection);
    
                // Add table to the scope
                serverScopeDescription.Tables.Add(table);
    
    
                /* Provisioning the server */
    
                // Configure the source/server replica
                SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(serverScopeDescription);
    
                // Don't create the table on the source/server
                serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip); //TODO: Incorrect sample (SetCreateTableDefault) http://msdn.microsoft.com/en-us/library/dd918848(SQL.105).aspx
    
                if (filterColumn != String.Empty & filterColumn != null)
                {
    
                    // Define filters for  table
                    serverConfig[tableName].AddFilterColumn(filterColumn);
    
                    if (filterKey != String.Empty & filterKey != null)
                    {
                        serverConfig[tableName].FilterClause = String.Format("[side]." + filterColumn + "={0}", filterKey);
                    }
    
                }
    The main draw back right now, IMO is that the filter key is static and once you define it at provisioning time, you are limited to that filter. This means that you should design your scopes with corresponding filters in mind.

    Hope this helps.
    Rick
    Wednesday, August 12, 2009 3:01 PM
  • Ok. Thanks.
    Now i need to filter with a paramenter. How can i do that?
    Thxs!
    Wednesday, August 12, 2009 3:51 PM
  • You've just hit my biggest pain pain point with filtering. This is not currenly possible with the latest bits as far as I know. The recommendation is to design your scopes based on static filter keys.

    Sync Team, if this has changed or there are any plans to add the support for dynamic filter parameters, please please advise.

    Thanks,

    Rick
    Rick
    • Marked as answer by EricDelahaye Wednesday, August 12, 2009 6:05 PM
    Wednesday, August 12, 2009 4:35 PM
  • Thank you.
    I hope that this feature is possible in the future.
    Wednesday, August 12, 2009 6:06 PM
  • Ups i have a problem with  filters and scopes. A realize that if i put a filter base on a primary key column then the sinchronize process not working. just not work and no message error and nothing, for example SessionProgress is not raised. But i have a little test database and the filter work fine. I don't know i am very frustrated.
    My filter has a IN clause and a function that get de list to fill the IN, if i hardcode the IN values the sync work fine too, then my conclusion is that the filter with the function have a problem. When i run this manually it work but with out message error and track information i'm lost.


    Thanks,

    Wednesday, August 26, 2009 6:06 PM
  • Ok I already have the problem a little more isolated:

    I have the follow filter

    serverConfig["Organization"].AddFilterColumn("id_organization");
    serverConfig["Organization"].FilterClause = string.Format("[side].id_organization in (select id_organization from GetMyOrganizations('17A367A1-6282-42F0-8128-1F4DE46BCFFA'))");

    GetMyOrganizations is a function that resolve a list of id_organization, these function works against other database.


    Thursday, August 27, 2009 6:03 PM
  • Yes!
    I need to put allow snapshot isolation on the other database. Thank you Sql Profiler!!!

    ALLOW_SNAPSHOT_ISOLATION

     

    ON

    • Marked as answer by EricDelahaye Thursday, August 27, 2009 6:59 PM
    Thursday, August 27, 2009 6:59 PM