Modifying Sync Framework Scope Definition – Part 3 – Workarounds – Adding/Removing Columns


Finally, I found some time to  continue this series on modifying Sync Framework scope definition.

As I have previously mentioned in Part 2 of this series, to add or remove columns from a scope definition, the following are the affected objects:

1. Add/Remove column

(Assuming the column is not a PK or a FilterColumn)

  • modify select_changes SP
  • modify select_row SP
  • modify Insert/Update SP
  • drop Bulk Insert/Update /Delete SP (they reference the bulktype and we can’t re-create the bulk type without dropping them)
  • drop and recreate BulkType (there is no ALTER TYPE in SQL Server)
  • recreate Bulk Insert/Update /Delete SP
  • modify config_data column in the scope_config table

While you can directly modify the above objects, it’s cumbersome and error-prone.

So in this post, I’ll show you how by using Sync Framework Provisioning’s Script() method, you can programmatically manipulate the script to effect the addition/removal of columns from a scope definition.

Please note that this is just a workaround and may leave your scope definition unusable, so backup first, test and run it at your own risks.

Code Snippet
  1. //setup the server connection
  2. var serverConn = new SqlConnection("Data Source=localhost; Initial Catalog=Aggregator; Integrated Security=True");
  3.            
  4. const string scopeName = "TestScope";
  5.            
  6. // define a new scope
  7. var scopeDesc = new DbSyncScopeDescription(scopeName);
  8.  
  9. // get the description of the TestTable table from the server database
  10. var tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("TestTable", serverConn);
  11.  
  12. // add the table description to the sync scope definition
  13. scopeDesc.Tables.Add(tableDesc);
  14.  
  15. // set scope to be provisioned based on the scope definition
  16. var serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
  17.  
  18. if (!serverProvision.ScopeExists(scopeName))
  19. {
  20.     //apply the scope definition
  21.     serverProvision.Apply();
  22. }
  23. else
  24. {
  25.     // let's use Sync Fx's Script()
  26.     // set SetCreateProceduresDefault so Sync Fx thinks this is a new scope
  27.     // and create almost all the objects except triggers and tracking tables
  28.     serverProvision.SetCreateProceduresDefault(DbSyncCreationOption.Create);
  29.     serverProvision.SetUseBulkProceduresDefault(true);
  30.     var provisioningScript = serverProvision.Script();
  31.  
  32.     // drop statements for the affected objects
  33.     var stringBuilder = new StringBuilder();
  34.     stringBuilder.AppendLine("DROP PROCEDURE [TestTable_bulkinsert];");
  35.     stringBuilder.AppendLine("DROP PROCEDURE [TestTable_bulkupdate];");
  36.     stringBuilder.AppendLine("DROP PROCEDURE [TestTable_bulkdelete];");
  37.     stringBuilder.AppendLine("DROP TYPE [TestTable_BulkType];");
  38.     stringBuilder.AppendLine("DROP PROCEDURE [TestTable_selectchanges];");
  39.     stringBuilder.AppendLine("DROP PROCEDURE [TestTable_selectrow];");
  40.     stringBuilder.AppendLine("DROP PROCEDURE [TestTable_insert];");
  41.     stringBuilder.AppendLine("DROP PROCEDURE [TestTable_update];");
  42.     stringBuilder.AppendLine("DROP PROCEDURE [TestTable_delete];");
  43.     stringBuilder.AppendLine("DROP PROCEDURE [TestTable_insertmetadata];");
  44.     stringBuilder.AppendLine("DROP PROCEDURE [TestTable_updatemetadata];");
  45.     stringBuilder.AppendLine("DROP PROCEDURE [TestTable_deletemetadata];");
  46.  
  47.     // append the sync provisioning script after the drop statements
  48.     var alterScopeSql = stringBuilder.Append(provisioningScript).ToString();
  49.  
  50.     // extract the config_data entry from the script
  51.     int x = alterScopeSql.IndexOf("N'<SqlSyncProviderScopeConfiguration");
  52.     int y = alterScopeSql.IndexOf("</SqlSyncProviderScopeConfiguration>");
  53.     var configEntry = alterScopeSql.Substring(x, y – x) + "</SqlSyncProviderScopeConfiguration>'";
  54.  
  55.     // remove the inserts for the scope_info and scope_config
  56.     x = alterScopeSql.IndexOf("– BEGIN Add scope");
  57.     y = alterScopeSql.IndexOf("– END Add Scope");
  58.     alterScopeSql = alterScopeSql.Remove(x, y – x);
  59.  
  60.     // replace the update scope_config to update the config_data column with the revised scope definition
  61.     alterScopeSql = alterScopeSql.Replace("scope_status = 'C'", "config_data=" + configEntry);
  62.  
  63.     // remove and replace the where clause to update the current scope with the revised config_data
  64.     // Note: make sure to prefix the scope_info table in the inner query with schema/object prefix if you used one
  65.     x = alterScopeSql.IndexOf("WHERE [config_id] =");
  66.     alterScopeSql = alterScopeSql.Remove(x, alterScopeSql.Length – x);
  67.     alterScopeSql = alterScopeSql
  68.         + " WHERE [config_id] = (SELECT scope_config_id FROM scope_info WHERE sync_scope_name='"
  69.         + scopeName + "')";
  70.  
  71.     using (var connection = new SqlConnection(serverConn.ConnectionString))
  72.     {
  73.         connection.Open();
  74.                     
  75.         // workaround for Incorrect Syntax near 'GO'… SqlCommand.ExecuteNonQuery(); error when statement contains GO
  76.         // WARNING: we are sending the commands separately and an error may leave the scope modification in an unusable state
  77.         string[] commands = alterScopeSql.Split(new string[] { "GO\r\n", "GO ", "GO\t", "GO" }, StringSplitOptions.RemoveEmptyEntries);
  78.         foreach (var c in commands)
  79.         {
  80.             var command = new SqlCommand(c, connection);
  81.             command.ExecuteNonQuery();
  82.         }
  83.     }
  84.  
  85. }

The code is pretty much self-documenting, so i won’t delve deep into the details.

The above code checks if the scope exists, if it doesn’t, it simply does the standard provisioning. If the scope exists, it generates the provisioning scripts and manipulates it.

What it essentially does is to recreate the affected objects above and update the scope_config’s config_data column with the revised scope definition.

You can test it by adding/removing columns from the tables you’re synching and checking that the objects above has been updated to reflect the new column list.

I’ll cover the remaining scope modification scenarios in subsequent posts.

Again, I’d love to hear any feedback.

Cheers,

JuneT

Advertisements

19 comments

  1. […] Modifying Sync Framework Scope Definition – Part 3 – Workarounds […]

  2. Hello

    Thank you for this post. It worked just fine for me and it is more maintainable than manually update all the metadata on sql side.

    In the following scenario:
    – I execute my schema changes both on client and server
    – run the above code server
    – run the above code on each client => is it possible for the client to update by itself the scope provisioning?
    – sync the data

    Angela

    1. if you mean the client updating itself based on the scope definition on the server, the answer is no.
      you need to run the workaround on each of the participants in the sync community.

  3. Hello
    Have you tried to achieve the same using Sql Compact? SqlCeSyncScopeProvisioning has no method to obtain the script that contains the SQL code to provision the database for a particular scope. Any idea how to make it work with Sql Ce?

    Angela

    1. just referencing the conversation on the forums for this: How to reprovision Sql Compact database, after schema update

  4. I need to run two scopes in parallel, on the same data, but the second scope has added columns. Modifying the original scope’s SP’s obviously won’t work for this. I’ve tried creating the new scope in its own SQL schema, but the _selectedchanges SPs are all created in the original table’s schema, so the provisioning tool gives an error. SetCreateProceduresForAdditionalScopeDefault only seems to work when everything happens in the same schema.

    1. unfortunately, that’s a limitation in the framework. you can manually create the stored procedures and the user defined table type for the second scope and manually hack the scope_config xml column to point to these new stored procedures instead of reusing the ones from the first scope.

  5. Hello Junet,

    this works fine if the added column of the old records have no data… if we do something like this to add a column: alter table TestTable add column_test varchar(30) not null default ‘XXXXXX’;
    the ‘XXXXXX’ of the existing records are not synchronized. Is there a workaround in this situation?
    Thanks

    1. that’s because the Alter table doesn’t mark the rows as updated (triggers not fired), you can simply do a dummy update (update tableX set col1=col1)

  6. Michael · · Reply

    Is it enough when the scope has been created with many tables and a new column added on only one table.. Thanks

    1. you should be able to modify just the one table.

  7. Hi June,

    The above solution is working when we create a scope from Template ?

    I tried this code but it generates an exception
    ‘Cannot alter CreateProcedures when a scope is being created from a template’

    Can you please help me in this ?

  8. Hi June,

    The above solution is working when we create a scope from Template ?

    I tried this code but it generates an exception
    ‘Cannot alter CreateProcedures when a scope is being created from a template’

    Can you please help me in this ?

    1. a scope thats being created from a template doesnt have the actual scope definition, it inherits it from the template.

  9. Hi June,
    I wanted to have confirmation about what the code above does : a new scope is created and currently existing scopes no longer sync (a Winrt client for exemple has to sync from zero again )

    1. this is a 3rd of a series on modifying scope definition… so not sure you how you ended up with a new scope definition… and new scopes wouldn’t normally break existing scopes…

      1. Yes, I messed up one during a test, but you are right. Thx JuneT

  10. […] Sync horizon doesn't support modifying a range definition, we will have to penetrate a sync horizon combined objects to simulate a new column… have a feeling during a illustration here […]

  11. Hi JuneT,
    How do we use this coding to add/remove column from a scope ,which is created using a template?
    or is there any alternative way?
    thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: