Modifying Sync Framework Scope Definition – Part 4 – Workarounds – Adding a Table to an existing scope


Found some time today to continue this series so let me cover adding a table to an existing scope.

As I have previously mentioned in Part 2 of this series, to add a new table to an existing scope, the following changes need to be made::

3. Add Table

  • add tracking table
  • populate tracking table
  • add insert/update/delete SP
  • add bulktype
  • add bulk insert/update/delete SP
  • add select_changes SP
  • add insert/update/delete metadata SP
  • add select_row SP
  • modify the config_data column in the scope_config table

To start with, here’s a sample code that provisions a scope with one table:

Code Snippet
  1. const string scopeName = “TestScope”;
  2. string alterScopeSql = string.Empty;
  3. //setup the server connection
  4. var serverConn = new SqlConnection(“Data Source=localhost; Initial Catalog=Aggregator; Integrated Security=True”);
  5. // define a new scope
  6. var scopeDesc = new DbSyncScopeDescription(scopeName);
  7. // get the description of the TestTable table from the server database
  8. var tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(“TestTable”, serverConn);
  9. // add the table description to the sync scope definition
  10. scopeDesc.Tables.Add(tableDesc);
  11. // set scope to be provisioned based on the scope definition
  12. var serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
  13. if (!serverProvision.ScopeExists(scopeName))
  14. {
  15.     //apply the scope definition
  16.     serverProvision.Apply();
  17. }
  18. else
  19. {
  20.     // let’s use Sync Fx’s Script()
  21.     // set SetCreateProceduresDefault to CreateOrUseExisting
  22.     // so Sync Fx doesnt generate stored proc scripts for existing tables
  23.     // but generate new ones for newly added table
  24.     serverProvision.SetCreateProceduresDefault(DbSyncCreationOption.CreateOrUseExisting);
  25.     serverProvision.SetUseBulkProceduresDefault(true);
  26.     // set SetCreateTrackingTableDefault to CreateOrUseExisting
  27.     // so Sync Fx doestn generate tracking table and triggers for existing table
  28.     // but generate tracking table and triggers for the newly added table
  29.     serverProvision.SetCreateTrackingTableDefault(DbSyncCreationOption.CreateOrUseExisting);
  30.     alterScopeSql = serverProvision.Script();
  31.     // extract the config_data entry from the script
  32.     int x = alterScopeSql.IndexOf(“N'<SqlSyncProviderScopeConfiguration”);
  33.     int y = alterScopeSql.IndexOf(“</SqlSyncProviderScopeConfiguration>”);
  34.     var configEntry = alterScopeSql.Substring(x, y – x) + “</SqlSyncProviderScopeConfiguration>'”;
  35.     // remove the inserts for the scope_info and scope_config
  36.     x = alterScopeSql.IndexOf(“– BEGIN Add scope”);
  37.     y = alterScopeSql.IndexOf(“– END Add Scope”);
  38.     alterScopeSql = alterScopeSql.Remove(x, y – x);
  39.     // replace the update scope_config to update the config_data column with the revised scope definition
  40.     alterScopeSql = alterScopeSql.Replace(“scope_status = ‘C'”, “config_data=” + configEntry);
  41.     // remove and replace the where clause to update the current scope with the revised config_data
  42.     // Note: make sure to prefix the scope_info table in the inner query with schema/object prefix if you used one
  43.     x = alterScopeSql.IndexOf(“WHERE [config_id] =”);
  44.     alterScopeSql = alterScopeSql.Remove(x, alterScopeSql.Length – x);
  45.     alterScopeSql = alterScopeSql
  46.         + ” WHERE [config_id] = (SELECT scope_config_id FROM scope_info WHERE sync_scope_name='”
  47.         + scopeName + “‘)”;
  48.     using (var connection = new SqlConnection(serverConn.ConnectionString))
  49.     {
  50.         connection.Open();
  51.         // workaround for Incorrect Syntax near ‘GO’… SqlCommand.ExecuteNonQuery(); error when statement contains GO
  52.         // WARNING: we are sending the commands separately and an error may leave the scope modification in an unusable state
  53.         string[] commands = alterScopeSql.Split(new string[] { “GO\r\n”, “GO “, “GO\t”, “GO” }, StringSplitOptions.RemoveEmptyEntries);
  54.         foreach (var c in commands)
  55.         {
  56.             var command = new SqlCommand(c, connection);
  57.             command.ExecuteNonQuery();
  58.         }
  59.     }
  60. }
  61. //setup the client connection
  62. var clientConn = new SqlConnection(“Data Source=localhost; Initial Catalog=Client1; Integrated Security=True”);
  63. // retrieve scope definition from server
  64. var clientScope = SqlSyncDescriptionBuilder.GetDescriptionForScope(scopeName, serverConn);
  65. // set scope to be provisioned based on the scope definition from server
  66. var clientProvision = new SqlSyncScopeProvisioning(clientConn, clientScope);
  67. if (!clientProvision.ScopeExists(scopeName))
  68. {
  69.     //apply the scope definition
  70.     clientProvision.Apply();
  71. }
  72. else
  73. {
  74.     // let’s modify scope by applying the same scope modification script applied on server
  75.     using (var connection = new SqlConnection(clientConn.ConnectionString))
  76.     {
  77.         connection.Open();
  78.         // workaround for Incorrect Syntax near ‘GO’… SqlCommand.ExecuteNonQuery(); error when statement contains GO
  79.         // WARNING: we are sending the commands separately and an error may leave the scope modification in an unusable state
  80.         string[] commands = alterScopeSql.Split(new string[] { “GO\r\n”, “GO “, “GO\t”, “GO” }, StringSplitOptions.RemoveEmptyEntries);
  81.         foreach (var c in commands)
  82.         {
  83.             var command = new SqlCommand(c, connection);
  84.             command.ExecuteNonQuery();
  85.         }
  86.     }
  87. }

And here’s the corresponding config_data entry in the scope_config table for the scope:

Code Snippet
  1. <SqlSyncProviderScopeConfigurationxmlns:xsi=http://www.w3.org/2001/XMLSchema-instancexmlns:xsd=http://www.w3.org/2001/XMLSchemaIsTemplate=false>
  2.   <AdapterName=[TestTable]GlobalName=[TestTable]
  3.            TrackingTable=[TestTable_tracking]
  4.            SelChngProc=[TestTable_selectchanges]
  5.            SelRowProc=[TestTable_selectrow]
  6.            InsProc=[TestTable_insert]
  7.            UpdProc=[TestTable_update]
  8.            DelProc=[TestTable_delete]
  9.            InsMetaProc=[TestTable_insertmetadata]
  10.            UpdMetaProc=[TestTable_updatemetadata]
  11.            DelMetaProc=[TestTable_deletemetadata]
  12.            BulkTableType=[TestTable_BulkType]
  13.            BulkInsProc=[TestTable_bulkinsert]
  14.            BulkUpdProc=[TestTable_bulkupdate]
  15.            BulkDelProc=[TestTable_bulkdelete]
  16.            InsTrig=[TestTable_insert_trigger]
  17.            UpdTrig=[TestTable_update_trigger]
  18.            DelTrig=[TestTable_delete_trigger]>
  19.     <Colname=Idtype=intparam=@P_1pk=true />
  20.     <Colname=testcolumntype=varcharsize=50null=trueparam=@P_2 />
  21.     <Colname=testcolumn2type=varcharsize=50null=trueparam=@P_3 />
  22.   </Adapter>
  23. </SqlSyncProviderScopeConfiguration>

Note that the above config_data entry has only one Adapter defined for the lone table added to our scope.

Now, let’s add a second table to our provisioning:

Code Snippet
  1. const string scopeName = “TestScope”;
  2. string alterScopeSql = string.Empty;
  3. //setup the server connection
  4. var serverConn = new SqlConnection(“Data Source=localhost; Initial Catalog=Aggregator; Integrated Security=True”);
  5. // define a new scope
  6. var scopeDesc = new DbSyncScopeDescription(scopeName);
  7. // get the description of the TestTable table from the server database
  8. var tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(“TestTable”, serverConn);
  9. // add the table description to the sync scope definition
  10. scopeDesc.Tables.Add(tableDesc);
  11. // let’s add a second table to demonstrate adding a new table to an existing scope
  12. tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(“TestTable2”, serverConn);
  13. scopeDesc.Tables.Add(tableDesc);
  14. // set scope to be provisioned based on the scope definition
  15. var serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
  16. if (!serverProvision.ScopeExists(scopeName))
  17. {
  18.     //apply the scope definition
  19.     serverProvision.Apply();
  20. }
  21. else
  22. {
  23.     // let’s use Sync Fx’s Script()
  24.     // set SetCreateProceduresDefault to CreateOrUseExisting
  25.     // so Sync Fx doesnt generate stored proc scripts for existing tables
  26.     // but generate new ones for newly added table
  27.     serverProvision.SetCreateProceduresDefault(DbSyncCreationOption.CreateOrUseExisting);
  28.     serverProvision.SetUseBulkProceduresDefault(true);
  29.     // set SetCreateTrackingTableDefault to CreateOrUseExisting
  30.     // so Sync Fx doestn generate tracking table and triggers for existing table
  31.     // but generate tracking table and triggers for the newly added table
  32.     serverProvision.SetCreateTrackingTableDefault(DbSyncCreationOption.CreateOrUseExisting);
  33.     alterScopeSql = serverProvision.Script();
  34.     // extract the config_data entry from the script
  35.     int x = alterScopeSql.IndexOf(“N'<SqlSyncProviderScopeConfiguration”);
  36.     int y = alterScopeSql.IndexOf(“</SqlSyncProviderScopeConfiguration>”);
  37.     var configEntry = alterScopeSql.Substring(x, y – x) + “</SqlSyncProviderScopeConfiguration>'”;
  38.     // remove the inserts for the scope_info and scope_config
  39.     x = alterScopeSql.IndexOf(“– BEGIN Add scope”);
  40.     y = alterScopeSql.IndexOf(“– END Add Scope”);
  41.     alterScopeSql = alterScopeSql.Remove(x, y – x);
  42.     // replace the update scope_config to update the config_data column with the revised scope definition
  43.     alterScopeSql = alterScopeSql.Replace(“scope_status = ‘C'”, “config_data=” + configEntry);
  44.     // remove and replace the where clause to update the current scope with the revised config_data
  45.     // Note: make sure to prefix the scope_info table in the inner query with schema/object prefix if you used one
  46.     x = alterScopeSql.IndexOf(“WHERE [config_id] =”);
  47.     alterScopeSql = alterScopeSql.Remove(x, alterScopeSql.Length – x);
  48.     alterScopeSql = alterScopeSql
  49.         + ” WHERE [config_id] = (SELECT scope_config_id FROM scope_info WHERE sync_scope_name='”
  50.         + scopeName + “‘)”;
  51.     using (var connection = new SqlConnection(serverConn.ConnectionString))
  52.     {
  53.         connection.Open();
  54.         // workaround for Incorrect Syntax near ‘GO’… SqlCommand.ExecuteNonQuery(); error when statement contains GO
  55.         // WARNING: we are sending the commands separately and an error may leave the scope modification in an unusable state
  56.         string[] commands = alterScopeSql.Split(new string[] { “GO\r\n”, “GO “, “GO\t”, “GO” }, StringSplitOptions.RemoveEmptyEntries);
  57.         foreach (var c in commands)
  58.         {
  59.             var command = new SqlCommand(c, connection);
  60.             command.ExecuteNonQuery();
  61.         }
  62.     }
  63. }
  64. //setup the client connection
  65. var clientConn = new SqlConnection(“Data Source=localhost; Initial Catalog=Client1; Integrated Security=True”);
  66. // retrieve scope definition from server
  67. var clientScope = SqlSyncDescriptionBuilder.GetDescriptionForScope(scopeName, serverConn);
  68. // set scope to be provisioned based on the scope definition from server
  69. var clientProvision = new SqlSyncScopeProvisioning(clientConn, clientScope);
  70. if (!clientProvision.ScopeExists(scopeName))
  71. {
  72.     //apply the scope definition
  73.     clientProvision.Apply();
  74. }
  75. else
  76. {
  77.     // let’s modify scope by applying the same scope modification script applied on server
  78.     using (var connection = new SqlConnection(clientConn.ConnectionString))
  79.     {
  80.         connection.Open();
  81.         // workaround for Incorrect Syntax near ‘GO’… SqlCommand.ExecuteNonQuery(); error when statement contains GO
  82.         // WARNING: we are sending the commands separately and an error may leave the scope modification in an unusable state
  83.         string[] commands = alterScopeSql.Split(new string[] { “GO\r\n”, “GO “, “GO\t”, “GO” }, StringSplitOptions.RemoveEmptyEntries);
  84.         foreach (var c in commands)
  85.         {
  86.             var command = new SqlCommand(c, connection);
  87.             command.ExecuteNonQuery();
  88.         }
  89.     }
  90. }

The above now has 2 tables in the scope definition. Since our scope already exists, the provisioning branches to the part of the code where we generate the provisioning scripts.

This is how the script looks like before we further customize it.

Code Snippet
  1. — BEGIN Enable Snapshot Isolation on Database ‘Aggregator’ if needed
  2. IF EXISTS (SELECT NAME FROM sys.databases where NAME = N‘Aggregator’ AND [snapshot_isolation_state] = 0)
  3. BEGIN
  4.     ALTER DATABASE [Aggregator] SET ALLOW_SNAPSHOT_ISOLATION ON
  5. END
  6. GO
  7. — END Enable Snapshot Isolation on Database ‘Aggregator’ if needed
  8. — BEGIN Create Scope Info Table named [scope_info]
  9. IF NOT EXISTS (SELECT t.name FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.name = N‘scope_info’ AND s.name = N‘dbo’)
  10. BEGIN
  11.     CREATE TABLE [scope_info] ([scope_local_id] int IDENTITY(1,1) NOT NULL, [scope_id] uniqueidentifier DEFAULT NEWID() NOT NULL, [sync_scope_name] nvarchar(100) NOT NULL, [scope_sync_knowledge] varbinary(max) NULL, [scope_tombstone_cleanup_knowledge] varbinary(max) NULL, [scope_timestamp] timestamp NULL, [scope_config_id] uniqueidentifier NULL, [scope_restore_count] int DEFAULT 0 NOT NULL, [scope_user_comment] nvarchar(max) NULL)
  12.     ALTER TABLE [scope_info] ADD CONSTRAINT [PK_scope_info] PRIMARY KEY ([sync_scope_name])
  13. END
  14. GO
  15. — END Create Scope Info Table named [scope_info]
  16. — BEGIN Create Scope Config Table named [scope_config]
  17. IF NOT EXISTS (SELECT t.name FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.name = N‘scope_config’ AND s.name = N‘dbo’)
  18. BEGIN
  19.     CREATE TABLE [scope_config] ([config_id] uniqueidentifier NOT NULL, [config_data] xml NOT NULL, [scope_status] char NULL)
  20.     ALTER TABLE [scope_config] ADD CONSTRAINT [PK_scope_config] PRIMARY KEY ([config_id])
  21. END
  22. GO
  23. — END Create Scope Config Table named [scope_config]
  24. — BEGIN Create Schema Info Table named [schema_info]
  25. IF NOT EXISTS (SELECT t.name FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.name = N‘schema_info’ AND s.name = N‘dbo’)
  26. BEGIN
  27.     CREATE TABLE [schema_info] ([schema_major_version] integer NOT NULL, [schema_minor_version] integer NOT NULL, [schema_extended_info] nvarchar(100) NOT NULL)
  28.     ALTER TABLE [schema_info] ADD CONSTRAINT [PK_schema_info] PRIMARY KEY ([schema_major_version], [schema_minor_version])
  29. END
  30. GO
  31. — END Create Schema Info Table named [schema_info]
  32. — BEGIN Add version info for schema
  33. IF NOT EXISTS (SELECT [schema_major_version] FROM [schema_info]) INSERT INTO [schema_info] ([schema_major_version], [schema_minor_version], [schema_extended_info]) VALUES (2, 1, N)
  34. GO
  35. — END Add version info for schema
  36. — BEGIN Add scope configuration entry for Config ID ‘8434648a-c925-463e-b952-d10892bac7bc’
  37. INSERT INTO [scope_config] ([config_id], [config_data], [scope_status]) VALUES (‘8434648a-c925-463e-b952-d10892bac7bc’, N‘<SqlSyncProviderScopeConfiguration xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance&#8221; xmlns:xsd=”http://www.w3.org/2001/XMLSchema&#8221; IsTemplate=”false”><Adapter Name=”[TestTable]” GlobalName=”[TestTable]” TrackingTable=”[TestTable_tracking]” SelChngProc=”[TestTable_selectchanges]” SelRowProc=”[TestTable_selectrow]” InsProc=”[TestTable_insert]” UpdProc=”[TestTable_update]” DelProc=”[TestTable_delete]” InsMetaProc=”[TestTable_insertmetadata]” UpdMetaProc=”[TestTable_updatemetadata]” DelMetaProc=”[TestTable_deletemetadata]” BulkTableType=”[TestTable_BulkType]” BulkInsProc=”[TestTable_bulkinsert]” BulkUpdProc=”[TestTable_bulkupdate]” BulkDelProc=”[TestTable_bulkdelete]” InsTrig=”[TestTable_insert_trigger]” UpdTrig=”[TestTable_update_trigger]” DelTrig=”[TestTable_delete_trigger]”><Col name=”Id” type=”int” param=”@P_1″ pk=”true” /><Col name=”testcolumn” type=”varchar” size=”50″ null=”true” param=”@P_2″ /><Col name=”testcolumn2″ type=”varchar” size=”50″ null=”true” param=”@P_3″ /></Adapter><Adapter Name=”[TestTable2]” GlobalName=”[TestTable2]” TrackingTable=”[TestTable2_tracking]” SelChngProc=”[TestTable2_selectchanges]” SelRowProc=”[TestTable2_selectrow]” InsProc=”[TestTable2_insert]” UpdProc=”[TestTable2_update]” DelProc=”[TestTable2_delete]” InsMetaProc=”[TestTable2_insertmetadata]” UpdMetaProc=”[TestTable2_updatemetadata]” DelMetaProc=”[TestTable2_deletemetadata]” BulkTableType=”[TestTable2_BulkType]” BulkInsProc=”[TestTable2_bulkinsert]” BulkUpdProc=”[TestTable2_bulkupdate]” BulkDelProc=”[TestTable2_bulkdelete]” InsTrig=”[TestTable2_insert_trigger]” UpdTrig=”[TestTable2_update_trigger]” DelTrig=”[TestTable2_delete_trigger]”><Col name=”id” type=”int” idSeed=”0″ idStep=”1″ param=”@P_1″ pk=”true” /><Col name=”testcolumn” type=”varchar” size=”50″ null=”true” param=”@P_2″ /><Col name=”timecolumn” type=”time” null=”true” param=”@P_3″ /></Adapter></SqlSyncProviderScopeConfiguration>’, ‘P’)
  38. GO
  39. — END Add scope configuration entry for Config ID ‘8434648a-c925-463e-b952-d10892bac7bc’
  40. — BEGIN Add Scope Info entry for Scope ‘TestScope’
  41. INSERT INTO [scope_info] ([sync_scope_name], [scope_config_id], [scope_user_comment]) VALUES (N‘TestScope’, ‘8434648a-c925-463e-b952-d10892bac7bc’, N)
  42. GO
  43. — END Add Scope Info entry for Scope ‘TestScope’
  44. UPDATE [scope_config] SET scope_status = ‘C’
  45. WHERE [config_id] = ‘8434648a-c925-463e-b952-d10892bac7bc’;

You’ll notice that it doesn’t contain any script for the table that is already part of the scope.

The lines:

 serverProvision.SetCreateProceduresDefault(DbSyncCreationOption.CreateOrUseExisting);

ServerProvision.SetCreateTrackingTableDefault(DbSyncCreationOption.CreateOrUseExisting);

instructs Sync Framework to use the existing stored procedures, tracking tables and triggers  if they already exists which is true in the case of our first table. Thus, Sync Framework only generates scripts for the new table.

The following is the  altered provisioning script after we customized it:

Code Snippet
  1. — BEGIN Enable Snapshot Isolation on Database ‘Aggregator’ if needed
  2. IF EXISTS (SELECT NAME FROM sys.databases where NAME = N‘Aggregator’ AND [snapshot_isolation_state] = 0)
  3. BEGIN
  4.     ALTER DATABASE [Aggregator] SET ALLOW_SNAPSHOT_ISOLATION ON
  5. END
  6. GO
  7. — END Enable Snapshot Isolation on Database ‘Aggregator’ if needed
  8. — BEGIN Create Scope Info Table named [scope_info]
  9. IF NOT EXISTS (SELECT t.name FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.name = N‘scope_info’ AND s.name = N‘dbo’)
  10. BEGIN
  11.     CREATE TABLE [scope_info] ([scope_local_id] int IDENTITY(1,1) NOT NULL, [scope_id] uniqueidentifier DEFAULT NEWID() NOT NULL, [sync_scope_name] nvarchar(100) NOT NULL, [scope_sync_knowledge] varbinary(max) NULL, [scope_tombstone_cleanup_knowledge] varbinary(max) NULL, [scope_timestamp] timestamp NULL, [scope_config_id] uniqueidentifier NULL, [scope_restore_count] int DEFAULT 0 NOT NULL, [scope_user_comment] nvarchar(max) NULL)
  12.     ALTER TABLE [scope_info] ADD CONSTRAINT [PK_scope_info] PRIMARY KEY ([sync_scope_name])
  13. END
  14. GO
  15. — END Create Scope Info Table named [scope_info]
  16. — BEGIN Create Scope Config Table named [scope_config]
  17. IF NOT EXISTS (SELECT t.name FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.name = N‘scope_config’ AND s.name = N‘dbo’)
  18. BEGIN
  19.     CREATE TABLE [scope_config] ([config_id] uniqueidentifier NOT NULL, [config_data] xml NOT NULL, [scope_status] char NULL)
  20.     ALTER TABLE [scope_config] ADD CONSTRAINT [PK_scope_config] PRIMARY KEY ([config_id])
  21. END
  22. GO
  23. — END Create Scope Config Table named [scope_config]
  24. — BEGIN Create Schema Info Table named [schema_info]
  25. IF NOT EXISTS (SELECT t.name FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.name = N‘schema_info’ AND s.name = N‘dbo’)
  26. BEGIN
  27.     CREATE TABLE [schema_info] ([schema_major_version] integer NOT NULL, [schema_minor_version] integer NOT NULL, [schema_extended_info] nvarchar(100) NOT NULL)
  28.     ALTER TABLE [schema_info] ADD CONSTRAINT [PK_schema_info] PRIMARY KEY ([schema_major_version], [schema_minor_version])
  29. END
  30. GO
  31. — END Create Schema Info Table named [schema_info]
  32. — BEGIN Add version info for schema
  33. IF NOT EXISTS (SELECT [schema_major_version] FROM [schema_info]) INSERT INTO [schema_info] ([schema_major_version], [schema_minor_version], [schema_extended_info]) VALUES (2, 1, N)
  34. GO
  35. — END Add version info for schema
  36. — END Add Scope Info entry for Scope ‘TestScope’
  37. UPDATE [scope_config] SET config_data=N‘<SqlSyncProviderScopeConfiguration xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance&#8221; xmlns:xsd=”http://www.w3.org/2001/XMLSchema&#8221; IsTemplate=”false”><Adapter Name=”[TestTable]” GlobalName=”[TestTable]” TrackingTable=”[TestTable_tracking]” SelChngProc=”[TestTable_selectchanges]” SelRowProc=”[TestTable_selectrow]” InsProc=”[TestTable_insert]” UpdProc=”[TestTable_update]” DelProc=”[TestTable_delete]” InsMetaProc=”[TestTable_insertmetadata]” UpdMetaProc=”[TestTable_updatemetadata]” DelMetaProc=”[TestTable_deletemetadata]” BulkTableType=”[TestTable_BulkType]” BulkInsProc=”[TestTable_bulkinsert]” BulkUpdProc=”[TestTable_bulkupdate]” BulkDelProc=”[TestTable_bulkdelete]” InsTrig=”[TestTable_insert_trigger]” UpdTrig=”[TestTable_update_trigger]” DelTrig=”[TestTable_delete_trigger]”><Col name=”Id” type=”int” param=”@P_1″ pk=”true” /><Col name=”testcolumn” type=”varchar” size=”50″ null=”true” param=”@P_2″ /><Col name=”testcolumn2″ type=”varchar” size=”50″ null=”true” param=”@P_3″ /></Adapter><Adapter Name=”[TestTable2]” GlobalName=”[TestTable2]” TrackingTable=”[TestTable2_tracking]” SelChngProc=”[TestTable2_selectchanges]” SelRowProc=”[TestTable2_selectrow]” InsProc=”[TestTable2_insert]” UpdProc=”[TestTable2_update]” DelProc=”[TestTable2_delete]” InsMetaProc=”[TestTable2_insertmetadata]” UpdMetaProc=”[TestTable2_updatemetadata]” DelMetaProc=”[TestTable2_deletemetadata]” BulkTableType=”[TestTable2_BulkType]” BulkInsProc=”[TestTable2_bulkinsert]” BulkUpdProc=”[TestTable2_bulkupdate]” BulkDelProc=”[TestTable2_bulkdelete]” InsTrig=”[TestTable2_insert_trigger]” UpdTrig=”[TestTable2_update_trigger]” DelTrig=”[TestTable2_delete_trigger]”><Col name=”id” type=”int” idSeed=”0″ idStep=”1″ param=”@P_1″ pk=”true” /><Col name=”testcolumn” type=”varchar” size=”50″ null=”true” param=”@P_2″ /><Col name=”timecolumn” type=”time” null=”true” param=”@P_3″ /></Adapter></SqlSyncProviderScopeConfiguration>’
  38. WHERE [config_id] = (SELECT scope_config_id FROM scope_info WHERE sync_scope_name=‘TestScope’)

And here’s the new config_data entry in the scope_config entry for the scope.

Notice that we now have two Adapters defined.

If you explore the database using SQL Management Studio, you will find that an additional set of tracking table, stored procedures, UDT and triggers have been added.

Code Snippet
  1. <SqlSyncProviderScopeConfigurationxmlns:xsi=http://www.w3.org/2001/XMLSchema-instancexmlns:xsd=http://www.w3.org/2001/XMLSchemaIsTemplate=false>
  2.   <AdapterName=[TestTable]
  3.            GlobalName=[TestTable]
  4.            TrackingTable=[TestTable_tracking]
  5.            SelChngProc=[TestTable_selectchanges]
  6.            SelRowProc=[TestTable_selectrow]
  7.            InsProc=[TestTable_insert]
  8.            UpdProc=[TestTable_update]
  9.            DelProc=[TestTable_delete]
  10.            InsMetaProc=[TestTable_insertmetadata]
  11.            UpdMetaProc=[TestTable_updatemetadata]
  12.            DelMetaProc=[TestTable_deletemetadata]
  13.            BulkTableType=[TestTable_BulkType]
  14.            BulkInsProc=[TestTable_bulkinsert]
  15.            BulkUpdProc=[TestTable_bulkupdate]
  16.            BulkDelProc=[TestTable_bulkdelete]
  17.            InsTrig=[TestTable_insert_trigger]
  18.            UpdTrig=[TestTable_update_trigger]
  19.            DelTrig=[TestTable_delete_trigger]>
  20.     <Colname=Idtype=intparam=@P_1pk=true />
  21.     <Colname=testcolumntype=varcharsize=50null=trueparam=@P_2 />
  22.     <Colname=testcolumn2type=varcharsize=50null=trueparam=@P_3 />
  23.   </Adapter>
  24.   <AdapterName=[TestTable2]
  25.            GlobalName=[TestTable2]
  26.            TrackingTable=[TestTable2_tracking]
  27.            SelChngProc=[TestTable2_selectchanges]
  28.            SelRowProc=[TestTable2_selectrow]
  29.            InsProc=[TestTable2_insert]
  30.            UpdProc=[TestTable2_update]
  31.            DelProc=[TestTable2_delete]
  32.            InsMetaProc=[TestTable2_insertmetadata]
  33.            UpdMetaProc=[TestTable2_updatemetadata]
  34.            DelMetaProc=[TestTable2_deletemetadata]
  35.            BulkTableType=[TestTable2_BulkType]
  36.            BulkInsProc=[TestTable2_bulkinsert]
  37.            BulkUpdProc=[TestTable2_bulkupdate]
  38.            BulkDelProc=[TestTable2_bulkdelete]
  39.            InsTrig=[TestTable2_insert_trigger]
  40.            UpdTrig=[TestTable2_update_trigger]
  41.            DelTrig=[TestTable2_delete_trigger]>
  42.     <Colname=idtype=intidSeed=0idStep=1param=@P_1pk=true />
  43.     <Colname=testcolumntype=varcharsize=50null=trueparam=@P_2 />
  44.     <Colname=timecolumntype=timenull=trueparam=@P_3 />
  45.   </Adapter>
  46. </SqlSyncProviderScopeConfiguration>

Again, I’d love to hear any feedback.

Until next time.

Cheers,

JuneT

Advertisements

13 comments

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

  2. Thanks for this series of articles. I have used the knowledge to create classes that handle table adds and column updates.

    I need to extend this further to handle filtered scopes. I guess my first question is: do you intend to write an article about this?

    My second question is about filter clauses. Where to they exist in the server database?

    As an example I have the following clause, but cannot locate it:

    “[side].AnswerId IN (Select Answer.AnswerId From Answer WHERE Answer.AssessmentId = @idParameter)”

    1. Hi Shawn,

      Yes I intend to write a couple more posts to complete the series. I just havent found time lately to do some blogging because of project work.

      Thanks!

      JuneT

  3. forget about my second question. It’s in config_data…

  4. I have need to remove a table from an existing scope. You mentioned in a prior post that this requires tinking with the sync knowledge. Any pointers on that somewhere? Thanks.

    1. @shane – if you dont mind having the knowledge entries for the rows in the table to be de-scoped, just remove it from the scope definition (scope_config entry).

      removing the sync knowledge requires tinkering with the tracking tables to mark the rows as deleted (tombstone) and doing a metadata cleanup which is not a straightforward process (and the reason why i havent posted it).

  5. Hi JuneT, thanks for sharing your knowledge, it helps. Regarding adding a new table to the scope – there are three triggers Sync Framework adds to the table when provisioning (tbl_delete_trigger, tbl_insert_trigger, tbl_update_trigger). I couldn’t see you mention those triggers in the scenario and can’t see them scripted in your example. Did I miss something?

  6. Hi JuneT, please disregard my question – the triggers creation is handled by tracking tables creation script – I can see them in the script that is generated by your code. Besides I noticed you mention the triggers in one of the comments:
    // so Sync Fx doestn generate tracking table and triggers for existing table.
    All good. Thanks again!

  7. Thanks for the post. Have one problem, new table is created in server database (not in client) and running the code creating everything in server right. But in client side the new table is not created. and giving error as table not found. Please give me a reply. Thanks

    1. sync framework doesnt do schema syncs. if the table was not included when you provision the scope, it will not pick up and include any other new or existing table that was not part of the provisioning.

  8. Hi JuneT,

    Is it possible to create a snapshot of provisioned client database,therefore can use it for multiple clients rather than provisioning each client.

    1. you can create a backup of the database, restore on client and make sure you run PerformPostRestoreFixup before the first sync

  9. I have successfully added a new table to an existing scope. thank you for the tutorial.
    BTW, the problem arose was,
    when I add an empty table to an existing scope( already synced several times and no further uploads & downloads), why the sync happens for all the tables like the initial sync?
    This shouldn’t take too much time since we added an empty table. but when I sync ,it takes longer time like the initial sync. How to get rid of this?

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: