locked
Problems trying to create filtered scope RRS feed

  • Question

  • Hi,
    based on http://jtabadero.wordpress.com/2010/09/02/sync-framework-provisioning/ and http://207.46.16.248/en-us/library/ff928701%28SQL.110%29.aspx I am trying to provision a filtered scope and template for the table "customer"
      
    First I created the template like:
      
    Dim scopeDesc As New DbSyncScopeDescription("PersonalNummerFilteredTemplate")
    Dim serverConn As SqlConnection = Me.serverConnection
    scopeDesc.UserComment = "Template for Customer, filtered by PersonalNummer"
    Dim customerDescription As DbSyncTableDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable("Customer", serverConn)
    scopeDesc.Tables.Add(customerDescription)
    Dim serverTemplate As New SqlSyncScopeProvisioning(serverConn, scopeDesc, SqlSyncScopeProvisioningType.Template)
    serverTemplate.Tables("Customer").AddFilterColumn("PersonalNummer")
    serverTemplate.Tables("Customer").FilterClause = "[side].[PersonalNummer] = @PersonalNummer"
    Dim param As New SqlParameter("@PersonalNummer", SqlDbType.NVarChar, 8)
    serverTemplate.Tables("Customer").FilterParameters.Add(param)
    serverTemplate.Apply()

    looking at the table scope_templates I see one row with my template "PersonalNummerFilteredTemplate"
    and in the table scope_config, column config_data I see
               
    <SqlSyncProviderScopeConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" IsTemplate="true">
      <Adapter Name="[Customer]" GlobalName="[Customer]" TrackingTable="[Customer_tracking]" SelChngProc="[Customer_selectchanges]" SelRowProc="[Customer_selectrow]" InsProc="[Customer_insert]" UpdProc="[Customer_update]" DelProc="[Customer_delete]" InsMetaProc="[Customer_insertmetadata]" UpdMetaProc="[Customer_updatemetadata]" DelMetaProc="[Customer_deletemetadata]" BulkTableType="[Customer_BulkType]" BulkInsProc="[Customer_bulkinsert]" BulkUpdProc="[Customer_bulkupdate]" BulkDelProc="[Customer_bulkdelete]" InsTrig="[Customer_insert_trigger]" UpdTrig="[Customer_update_trigger]" DelTrig="[Customer_delete_trigger]">
        <Col name="CustomerenNummer" type="nchar" size="10" param="@P_1" pk="true" />
        <Col name="PersonalNummer" type="nchar" size="8" param="@P_2" />
    ...
        <FilterParam name="@PersonalNummer" />
        <FilterClause>[side].[PersonalNummer] = @PersonalNummer</FilterClause>
        <FilterCol>PersonalNummer</FilterCol>
      </Adapter>
    </SqlSyncProviderScopeConfiguration>

    At http://207.46.16.248/en-us/library/gg294195%28SQL.110%29.aspx I read:

    "An entry is added to the scope_parameters table for the filter. The entries in this table contain XML that describes the filter parameters and their associated values. The parameters and values in this XML description are used by the adapter to specify the filter values during synchronization."

    However, this table does not contain any rows! Why not ? So something must be wrong in the way I create the template.


    Now I try to create a filtered scope "Customer_SomeValue" and provision the filtered template like this:

    Dim serverProvPersonalNummer As New SqlSyncScopeProvisioning(serverConnection)
    serverProvPersonalNummer.PopulateFromTemplate("Customer_SomeValue", "PersonalNummerFilteredTemplate")
    serverProvPersonalNummer.Tables("Customer").FilterParameters("@PersonalNummer").Value = "SomeValue"
    serverProvPersonalNummer.UserComment = "Customer data includes only for Representative SomeValue ."
    serverProvPersonalNummer.Apply()

    this fails at the line
    serverProvPersonalNummer.PopulateFromTemplate("Customer_SomeValue", "PersonalNummerFilteredTemplate")

    with the error

    System.IndexOutOfRangeException: SqlParameter mit ParameterName '@PersonalNummer' ist nicht in SqlParameterCollection enthalten. (= is not included in SqlParameterCollection)
       bei System.Data.SqlClient.SqlParameterCollection.GetParameter(String parameterName)
       bei System.Data.SqlClient.SqlParameterCollection.get_Item(String parameterName)
       bei Microsoft.Synchronization.Data.SqlServer.SqlSyncScopeProvisioning.PopulateFromTemplate(String scopeName, String templateName)
       bei SyncApplication.Form1.btn_provisionServerFiltered_Click(Object sender, EventArgs e) in ..
     
    What am I doing wrong?   

    Thanks Christian

    • Edited by chl-h Tuesday, September 6, 2011 10:09 AM
    Tuesday, September 6, 2011 9:53 AM

Answers

  • Hi,

    I think I found the solution to my problem at http://207.46.16.248/en-us/library/gg294195%28SQL.110%29.aspx

    "Because per-table elements are not changed when a new scope is added, all scopes that share a table must use the table in the same way. For example, a table is provisioned to include only two columns for scope A. Later, scope B is provisioned. Scope B contains the same table but specifies that all columns are included in the scope. Provisioning succeeds, but synchronization of scope B fails because the stored procedures used for synchronization of scope A, such as the selectchanges procedure, handle only the two columns specified by scope A, which does not match the expected set of columns synchronized by scope B.

    Configuration options for per-table elements are not applied when the element already exists in the database. For example, scope A is provisioned to use bulk procedures for the table it contains. Scope B is provisioned and specifies that bulk procedures should not be used for the same table. Because the table has already been provisioned, bulk procedures are used for this table for both scope A and scope B.

    You can specify that Sync Framework create a new selectchanges procedure to be used for a new scope that contains a table that has already been provisioned. This is useful when the second scope uses a different static row filter than the first. For example, scope A contains one table that is not filtered. Scope B contains the same table, and filters rows based on a filter clause of [side].[CustomerType] = ‘Retail’. When scope B is provisioned, specify Create for SqlSyncScopeProvisioning.SetCreateProceduresForAdditionalScopeDefault. This causes Sync Framework to create a second selectchanges procedure for enumerating changes in scope B. The filter column is also automatically added to the tracking table.

    Parameter-based filters accommodate multiple scopes by using extra tables that contain parameter and filter information. When an additional scope is provisioned with new filter parameters, the new scope reuses all of the existing elements for a table, adding only a row to the scope_parameters table that contains the parameter values for the scope. For more information on parameter-based filters, see How to: Filter Data for Database Synchronization (SQL Server)."

    As I had the table customer already included in another scope (for testing) removing that table from the scope solved that.

    • Marked as answer by chl-h Thursday, September 22, 2011 10:01 AM
    Tuesday, September 6, 2011 10:26 AM