Remove From My Forums

SqlSyncScopeProvisioning - How to change existing scope when using SqlSyncScopeProvisioning

Question
1

Sign in to vote

I've created a sync app using SqlSyncScopeProvisioning and have found that I need to change(Add) filters that were applied during the original scope provisioning. Does anyone know the steps involved in removing/redefining the scope?

Execution of the serverConfig.Apply line obviously fails indicating that the affected _Tracking tables already exist:

serverConfig.Apply((System.Data.SqlClient.SqlConnection)provider.Connection);

Deleting the tables has not solved the problem. What am I missing?

Current Code:

   public SqlSyncProvider ConfigureSqlSyncProvider(string hostName)
    {
      SqlSyncProvider provider = new SqlSyncProvider();
      provider.ScopeName = SyncUtils.ScopeName;

      provider.Connection = dbAction.ServerConnection;

      //create a new scope description and add the appropriate tables to this scope
      DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(SyncUtils.ScopeName);

      //class to be used to provision the scope defined above
      SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning();

      //determine if this scope already exists on the server and if not go ahead and provision

      if (!serverConfig.ScopeExists(SyncUtils.ScopeName, (System.Data.SqlClient.SqlConnection)provider.Connection))
      {
        System.Data.SqlClient.SqlConnection providerConnection = (System.Data.SqlClient.SqlConnection)provider.Connection;
        //add tables to this scope
        foreach (string tableName in SyncUtils.SyncAdapterTables)
          scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable(tableName, (System.Data.SqlClient.SqlConnection)provider.Connection));

        //note that it is important to call this after the tables have been added to the scope
        serverConfig.PopulateFromScopeDescription(scopeDesc);

        serverConfig = AddUserIdFilters(serverConfig, userId);
        serverConfig = FilterExpirationTables(serverConfig);
        serverConfig = AddExpirationFilterClause(serverConfig);
        serverConfig = AddDeletedFilter(serverConfig);

        //indicate that the base table already exists and does not need to be created
        serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);

        //provision the server
        serverConfig.Apply((System.Data.SqlClient.SqlConnection)provider.Connection);
      }

      //Register the BatchSpooled and BatchApplied events. These are fired when a provider is either enumerating or applying changes in batches.
      provider.BatchApplied += new EventHandler<DbBatchAppliedEventArgs>(provider_BatchApplied);
      provider.BatchSpooled += new EventHandler<DbBatchSpooledEventArgs>(provider_BatchSpooled);

      return provider;
    }

    private SqlSyncScopeProvisioning AddUserIdFilters(SqlSyncScopeProvisioning serverConfig, int userId)
    {
      Common.Helper.Debug("AddUserIdFilters");
      string[] userIdTables = { "UserName", "PushFileRecipientCE", "PushRecipient", "XferStatus" };
      foreach (string tableName in userIdTables)
        serverConfig = AddNumericFilter(serverConfig, tableName, "userId", "=", userId);

      string[] userIdTables2 = { "FileAccess", "SyncActivity", "SyncStats" };
      foreach (string tableName in userIdTables2)
        serverConfig = AddNumericFilter(serverConfig, tableName, "UserId", "=", userId);

      return serverConfig;
    }

    private SqlSyncScopeProvisioning AddDeletedFilter(SqlSyncScopeProvisioning serverConfig)
    {
      Common.Helper.Debug("AddDeletedFilters");
      string[] tables = { "FileName", "FilenameFilter", "Filter", "PushFileRecipientCE", "Push", "PushFile", "PushFileRecipient",
                          "PushFileRecipientFilter", "PushFilter", "PushRecipient", "PushRecipientActivity", "Store", "SyncActivity", "SyncStats",
                          "UserName", "XferStatus" };
      foreach (string tableName in tables)
        serverConfig = AddNumericFilter(serverConfig, tableName, "Deleted", "<", 1);

      return serverConfig;
    }

    private SqlSyncScopeProvisioning AddNumericFilter(SqlSyncScopeProvisioning serverConfig, string tableName, string filterName, string condition,
      object filterValue)
    {
      Common.Helper.Debug("AddNumericFilter");
      serverConfig.Tables[tableName].AddFilterColumn(filterName);
      serverConfig.Tables[tableName].FilterClause = string.Format("[side].[{0}] {1} {2}", tableName, condition, filterValue.ToString());
      return serverConfig;
    }

    private SqlSyncScopeProvisioning AddStringFilter(SqlSyncScopeProvisioning serverConfig, string tableName, string fieldName, string filterValue,
      string condition)
    {
      Common.Helper.Debug("AddStringFilter");
      serverConfig.Tables[tableName].AddFilterColumn(fieldName);
      serverConfig.Tables[tableName].FilterClause = string.Format("[side].[{0}] {1} '{2}'", tableName, condition, filterValue);

      return serverConfig;
    }

    private SqlSyncScopeProvisioning FilterExpirationTables(SqlSyncScopeProvisioning serverConfig)
    {
      Common.Helper.Debug("FilterExpirationTables");
      string[] expireTables = { "FileName", "Push" };
      foreach (string tableName in expireTables)
        serverConfig = AddStringFilter(serverConfig, tableName, "ExpirationDate", DateTime.Now.ToShortDateString(), "<=");

      return serverConfig;
    }

    private SqlSyncScopeProvisioning AddExpirationFilterClause(SqlSyncScopeProvisioning serverConfig)
    {
      Common.Helper.Debug("AddExpirationFilterClause");
      string expireTable = "PushFileRecipientCE";
      serverConfig = AddExpiresClause(serverConfig, expireTable, "ExpirationDate");
      serverConfig = AddExpiresClause(serverConfig, expireTable, "PushExpirationDate");

      return serverConfig;
    }

    private SqlSyncScopeProvisioning AddExpiresClause(SqlSyncScopeProvisioning serverConfig, string tableName, string expiresFieldName)
    {
      Common.Helper.Debug("AddExpiresClause");
      serverConfig.Tables[tableName].FilterClause = string.Format("[side].[{0}] >= '{1}'", expiresFieldName, DateTime.Now.ToShortDateString());
      return serverConfig;
    }

Wednesday, February 17, 2010 4:19 PM

Answers

1

Sign in to vote
I am not sure about your scenario requirement, but in case you just need to drop the provious provisioning and re-create it with some filterter column for synchornization, then here could be some steps you can try.

(You may want to backup this DB to prevent data loss.)

Here are steps if you just got one table previously provisioned.

0. From the scope_info table, find the entry where contains the information about the scope_name is the same as your previoius one.
1. Find the scope_config_id from the scope_info where the scope_name is the one you like to drop.
2. Goto the scope_config table, and get the config_data (a xml data) for this config id from #1.
3. Drop all Stored Procedures/Triggers as you can expand the config_data from #2 in the user table and this database.
4. Delete the scope config entry from scope_config table where scope_config_id is the same as #1.
5. Delete the scope info entry from the scope_info table where scope_name is the one you like to drop
6. Drop the corresponding *_tracking table.

7. Re-provisioning, including setting

SqlSyncScopeProvisioning.Tables["t1"].AddFilterColumn("filtered_column");
SqlSyncScopeProvisioning.Tables["t1"].FilterClause = "[side].[filtered_column] = 'filtered_data'";

Please try to include as many possible filtered columns as well.

Thanks.
Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
- Marked as answer by don.kirby Monday, February 22, 2010 12:32 PM
Thursday, February 18, 2010 12:18 AM

Answerer

hi don,

try this when adding your 2nd and subsequent scopes, make sure to skip creating most of the objects:

//skip creating the user tables
 serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);

//skip creating the change tracking tables
serverConfig.SetCreateTrackingTableDefault(DbSyncCreationOption.Skip);

//skip creating the change tracking triggers
serverConfig.SetCreateTriggersDefault(DbSyncCreationOption.Skip);

//skip creating the insert/update/delete/selectrow SPs including those for metadata
serverConfig.SetCreateProceduresDefault(DbSyncCreationOption.Skip);

//create new SelectChanges SPs for selecting changes for the new scope
//the new SelectChanges SPs will have a guid suffix
serverConfig.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create); 
            

serverConfig.Apply(serverConn);

Marked as answer by don.kirby Wednesday, March 3, 2010 8:11 PM

Sunday, February 21, 2010 8:13 AM

All replies

1

Sign in to vote

Instead of modifying the existing scope, please try to create a new scope with the filtering information as you would like to specify.

Thanks.
Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.

Wednesday, February 17, 2010 6:15 PM

Answerer
0

Sign in to vote

Hi, Thanks

Creating a new scope gives the same errors indicating that the _tracking tables already exist.
What am I doing wrong?

Wednesday, February 17, 2010 6:18 PM
0

Sign in to vote

try setting the SetCreateTrackingTableDefault as well.

Wednesday, February 17, 2010 10:04 PM
1

Sign in to vote
I am not sure about your scenario requirement, but in case you just need to drop the provious provisioning and re-create it with some filterter column for synchornization, then here could be some steps you can try.

(You may want to backup this DB to prevent data loss.)

Here are steps if you just got one table previously provisioned.

0. From the scope_info table, find the entry where contains the information about the scope_name is the same as your previoius one.
1. Find the scope_config_id from the scope_info where the scope_name is the one you like to drop.
2. Goto the scope_config table, and get the config_data (a xml data) for this config id from #1.
3. Drop all Stored Procedures/Triggers as you can expand the config_data from #2 in the user table and this database.
4. Delete the scope config entry from scope_config table where scope_config_id is the same as #1.
5. Delete the scope info entry from the scope_info table where scope_name is the one you like to drop
6. Drop the corresponding *_tracking table.

7. Re-provisioning, including setting

SqlSyncScopeProvisioning.Tables["t1"].AddFilterColumn("filtered_column");
SqlSyncScopeProvisioning.Tables["t1"].FilterClause = "[side].[filtered_column] = 'filtered_data'";

Please try to include as many possible filtered columns as well.

Thanks.
Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
- Marked as answer by don.kirby Monday, February 22, 2010 12:32 PM
Thursday, February 18, 2010 12:18 AM

Answerer

hi don,

try this when adding your 2nd and subsequent scopes, make sure to skip creating most of the objects:

//skip creating the user tables
 serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);

//skip creating the change tracking tables
serverConfig.SetCreateTrackingTableDefault(DbSyncCreationOption.Skip);

//skip creating the change tracking triggers
serverConfig.SetCreateTriggersDefault(DbSyncCreationOption.Skip);

//skip creating the insert/update/delete/selectrow SPs including those for metadata
serverConfig.SetCreateProceduresDefault(DbSyncCreationOption.Skip);

//create new SelectChanges SPs for selecting changes for the new scope
//the new SelectChanges SPs will have a guid suffix
serverConfig.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create); 
            

serverConfig.Apply(serverConn);

Marked as answer by don.kirby Wednesday, March 3, 2010 8:11 PM

Sunday, February 21, 2010 8:13 AM

0

Sign in to vote

Thanks.

Monday, February 22, 2010 12:29 PM
0

Sign in to vote

Thank you. You and L Zhou [MSFT] provided the information I needed. Each for a different scenario but I now I can choose.

Monday, February 22, 2010 12:29 PM
0

Sign in to vote

Hi,

I am working on the application for sync between SQL Server 2005. And I have same situation like I need to modify the existing scope(add new table/add filter etc.). So according to 'L Zhou' I need to reprovision the scope i.e. need to delete the respective entries from _scope table and drop the sync objects and then REPROVISION the scope with new modifications.

Now my question is : If I delete the respective entries from _scope tables (info & config), does the subsequent sync is going to affect? If the existing scope_sync_knowledge deleted and new one created, will the next sync request be able to detect the changes/knowledge?

Hope I have not confused you guys. I am new to the Microsoft Sync Framework.

Thanks for your help in advance.

-Ajinath

Monday, May 24, 2010 2:01 PM
0

Sign in to vote
I know this is an old post but I comes up at the top of a google search when looking to Deprovision a database.

It appears that there is a way to remove scopes: http://msdn.microsoft.com/en-us/library/ff928603.aspx

SqlSyncScopeDeprovisioning serverProvision = new SqlSyncScopeDeprovisioning(Conn); serverProvision.DeprovisionScope(REPORT_SCOPE);
Updated just for my fellow googlers.
Thursday, May 8, 2014 7:51 PM

Answered by:

SqlSyncScopeProvisioning - How to change existing scope when using SqlSyncScopeProvisioning

Question

Answers

All replies