Add filter to a table belonging to the schame

Aug 20, 2014 at 4:28 PM
Edited Aug 20, 2014 at 4:31 PM
Hi,
i am trying to publish a table named "Visits" belonging to a schema named "Jobs". Using the sync provisioning utility i have created a new scope with both the "Is template Scope" and "Enable Build Apply Procedures" checked. following is the code from the config file:
<SyncScope Name="MyFilteredScope" SchemaName="Jobs" IsTemplateScope="true" EnableBulkApplyProcedures="true">
            <SyncTables>
                <SyncTable Name="[Jobs].[Visits]" GlobalName="" SchemaName="" IncludeAllColumns="true" FilterClause="[side].CallSign = @CallSign">
                    <SyncColumns>
                        <SyncColumn Name="VisitID" GlobalName="" SqlType="uniqueidentifier" IsPrimaryKey="true" IsNullable="false" />
                        <SyncColumn Name="StatusID" GlobalName="" SqlType="tinyint" IsPrimaryKey="false" IsNullable="false" />
                        <SyncColumn Name="Date" GlobalName="" SqlType="datetime" IsPrimaryKey="false" IsNullable="true" />
                        <SyncColumn Name="Name" GlobalName="" SqlType="nvarchar" IsPrimaryKey="false" IsNullable="true" />
                        <SyncColumn Name="PolicyID" GlobalName="" SqlType="nvarchar" IsPrimaryKey="false" IsNullable="true" />
                        <SyncColumn Name="Address" GlobalName="" SqlType="nvarchar" IsPrimaryKey="false" IsNullable="true" />
                        <SyncColumn Name="Notes" GlobalName="" SqlType="nvarchar" IsPrimaryKey="false" IsNullable="true" />
                        <SyncColumn Name="ContactName" GlobalName="" SqlType="nvarchar" IsPrimaryKey="false" IsNullable="true" />
                        <SyncColumn Name="EmailAddress" GlobalName="" SqlType="nvarchar" IsPrimaryKey="false" IsNullable="true" />
                        <SyncColumn Name="Telephone" GlobalName="" SqlType="nvarchar" IsPrimaryKey="false" IsNullable="true" />
                        <SyncColumn Name="CallSign" GlobalName="" SqlType="nvarchar" IsPrimaryKey="false" IsNullable="true" />
                        <SyncColumn Name="REPLICATE" GlobalName="" SqlType="bit" IsPrimaryKey="false" IsNullable="false" />
                        <SyncColumn Name="LocationID" GlobalName="" SqlType="nvarchar" IsPrimaryKey="false" IsNullable="true" />
                        <SyncColumn Name="Postcode" GlobalName="" SqlType="nvarchar" IsPrimaryKey="false" IsNullable="true" />
                    </SyncColumns>
                    <FilterColumns>
                        <FilterColumn Name="CallSign" />
                    </FilterColumns>
                    <FilterParameters>
                        <FilterParameter Name="@CallSign" SqlType="nvarchar" DataSize="50" />
                    </FilterParameters>
                </SyncTable>
            </SyncTables>
        </SyncScope>
When i provision the DB using the above config, a table named "Jobs.scope_config" is created with the following value under the "config_data" column:
<SqlSyncProviderScopeConfiguration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" IsTemplate="true">
  <Adapter Name="[Jobs].[Visits]" GlobalName="[Jobs].[_[Visits]]" TrackingTable="[Jobs].[Visits_tracking]" SelChngProc="[Jobs].[Visits_selectchanges_d819f389-b493-40d5-adcc-0e95cdfa831d]" SelRowProc="[Jobs].[Visits_selectrow]" InsProc="[Jobs].[Visits_insert]" UpdProc="[Jobs].[Visits_update]" DelProc="[Jobs].[Visits_delete]" InsMetaProc="[Jobs].[Visits_insertmetadata]" UpdMetaProc="[Jobs].[Visits_updatemetadata]" DelMetaProc="[Jobs].[Visits_deletemetadata]" BulkTableType="[Jobs].[Visits_BulkType]" BulkInsProc="[Jobs].[Visits_bulkinsert]" BulkUpdProc="[Jobs].[Visits_bulkupdate]" BulkDelProc="[Jobs].[Visits_bulkdelete]" InsTrig="[Jobs].[Visits_insert_trigger]" UpdTrig="[Jobs].[Visits_update_trigger]" DelTrig="[Jobs].[Visits_delete_trigger]">
    <Col name="VisitID" type="uniqueidentifier" param="@P_1" pk="true" />
    <Col name="StatusID" type="tinyint" param="@P_2" />
    <Col name="Date" type="datetime" null="true" param="@P_3" />
    <Col name="Name" type="nvarchar" size="200" null="true" param="@P_4" />
    <Col name="PolicyID" type="nvarchar" size="50" null="true" param="@P_5" />
    <Col name="Address" type="nvarchar" size="500" null="true" param="@P_6" />
    <Col name="Notes" type="nvarchar" size="max" null="true" param="@P_7" />
    <Col name="ContactName" type="nvarchar" size="200" null="true" param="@P_8" />
    <Col name="EmailAddress" type="nvarchar" size="200" null="true" param="@P_9" />
    <Col name="Telephone" type="nvarchar" size="25" null="true" param="@P_10" />
    <Col name="CallSign" type="nvarchar" size="50" null="true" param="@P_11" />
    <Col name="REPLICATE" type="bit" param="@P_12" />
    <Col name="LocationID" type="nvarchar" size="50" null="true" param="@P_15" />
    <Col name="Postcode" type="nvarchar" size="8" null="true" param="@P_16" />
    <FilterParam name="@CallSign" />
    <FilterClause>[side].CallSign = @CallSign</FilterClause>
    <FilterCol>CallSign</FilterCol>
  </Adapter>
</SqlSyncProviderScopeConfiguration>
when I generate the server code the signature for class created for the publiched table is as follows:
[Microsoft.Synchronization.Services.SyncEntityTypeAttribute(TableGlobalName="Jobs__[Visits]", TableLocalName="[Jobs].[Visits]", KeyFields="VisitID")]
    public partial class Jobs__[Visits] : MyFilteredScopeOfflineEntityBase {
             //properties and variables have been removed
}
Inorder to compile the project i have to change the class name from "Jobs__[Visits]" to "Jobs__Visits" (note that i have to do a similar change in the SQLite client code generated). Here please note the value for the TableGlobalName, which is different from value stored in the Jobs.scope_config ([Jobs].[_[Visits]]).


Now when adding the filtering parameter what value should i provide for the tableName parameter, i.e. the 2nd parameter in the call to AddFilterParameterConfiguration? Running different combinations for the tableName parameters i am getting a NullReferenceException in "Microsoft.Synchronization.Services.SqlProvider.SqlSyncProviderService.CreateNewScopeForClient()" at the line
provisioning.Tables[param.TableName].FilterParameters[param.SqlParameterName].Value = param.Value;
in the above line of code the value for param.TableName is "Jobs__[Visits]" as provided to the AddFilterParameterConfiguration which in not found within the provisioning collection which contain the following values:
FilterClause: "[side].CallSign = @CallSign"
FilterColumns: Count = 1
FilterParameters: Count = 1
GlobalName: "[Jobs].[_[Visits].[]]"
LocalName: "[Jobs].[Visits]"
ObjectPrefix: ""
ObjectSchema: "Jobs"
UnquotedGlobalName: "Jobs._[Visits.]"
UnquotedLocalName: "Jobs.Visits"