Sync Framework scope filtering not working properly

Question

I have created a sync application for POS(point of sales) Application. One server is linked with multiple terminals. server and terminal databases have [pos_terminal] table. it has [POS_TERMINAL_ID] column as identity. This is how I provision the server.

            try
        {
            string strScopeName = "";
            strScopeName = "TERMINAL_SCOPE_T01";

            SqlConnection serverConn = new SqlConnection(strServer_Connection);

            //pos_terminal

            DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(strScopeName);
            DbSyncTableDescription pos_terminal_Description = SqlSyncDescriptionBuilder.GetDescriptionForTable("pos_terminal", serverConn);
            scopeDesc.Tables.Add(pos_terminal_Description);

            SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
            serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);

            serverProvision.ObjectSchema = "Sync";
            serverProvision.Tables["pos_terminal"].AddFilterColumn("POS_TERMINAL_ID");
            serverProvision.Tables["pos_terminal"].FilterClause = "[side].[POS_TERMINAL_ID]='T01'";

            if (serverProvision.ScopeExists(strScopeName))
            {
                return;
            }

            serverProvision.Apply();
            MessageBox.Show("Server provisioned with scope " + strScopeName);

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());


        }

and below , How I provision the terminal database.

try
        {
            string strScopeName = "";
            strScopeName = "TERMINAL_SCOPE_T01";

            SqlConnection clientConn = new SqlConnection(strTerimal_Connection);
            SqlConnection serverConn = new SqlConnection(strServer_Connection);
            DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope(strScopeName, null, "Sync", serverConn);
            SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, scopeDesc);


            clientProvision.ObjectSchema = "Sync";
            clientProvision.SetCreateTableDefault(DbSyncCreationOption.CreateOrUseExisting);

            if (clientProvision.ScopeExists(strScopeName))
            {
                return;
            }

            clientProvision.Apply();
            MessageBox.Show("Terminal provisioned with scope " + strScopeName);

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }

These all are basic filtering.

The problem is, in TERMINAL 01, when I set the POS_TERMINAL_ID to "T01"(in filtering) ,it works nicely and filtered data is synced with [pos_terminal] table. But in TERMINAL 02, when I set the POS_TERMINAL_ID to "T02"( in filtering), data are synced related to T01, but not T02.

same happens in TERMINAL 03, (synced T01, but not T03).

I have tested separately,by simply creating 2 tables in 2 databases and tested the coding. Coding works fine. There was a foreign key reference in another table to [POS_TERMINAL_ID]. I have removed it and also tested in POS database. but no luck.

Anyone having a solutions for such a scenario?

Thanks

Answer 1

you should specify SetCreateProceduresForAdditionalScopeDefault if you want to create a new stored procedure for the new scope. otherwise, the new scope will simply reuse the selectchanges stored procedure from the first scope.

better, use scope templates instead of adding new select changes stored procedure.

go thru this link to understand sync framework provisioning in detail...