none
With ADO.NET Provider, overlapping tables from different scopes causes errors on create RRS feed

  • Question

  • I'm using the below code to create a new scope and to provision it.  I understand from a previous post that I can have a table that exists in two separate scopes.  That is, say I have the following:

    • Scope1:   table1,table2
    • Scope2:  table2,table3

    When I use the below code, I get an error saying "There is already an object named table2_tracking' in the database" when it does the serverProvisioning.Apply(...).  I assume this is because that tracking table was already created in Scope1 when it was provisioned.

    So, my question is: "When creating overlapping scope's, how do I get the overlapping table in both scopes?"

    Thanks

     

        /// <summary>
        /// Verifies that we have a Schema "sync", then creates a new scope using the current list
        /// of tables passed in
        /// </summary>
        /// <param name="scopeName"></param>
        /// <param name="connectionString"></param>
        /// <param name="catalogName"></param>
        /// <param name="tablesToCreateScopeWith"></param>
        internal static void CreateScope(string scopeName, 
          string connectionString, 
          string catalogName,
          List<string> tablesToCreateScopeWith)
        {
          const string schemaName = "sync";
    
          // verify we have a schema sync
          SMOUtils.CreateSchema(schemaName, connectionString, catalogName);
    
          var provider =
            new SqlSyncProvider
              {
                ScopeName = scopeName,
                Connection = new SqlConnection(connectionString)
              };
    
          //create a new scope description and add the appropriate tables to this scope
          var scopeDesc = new DbSyncScopeDescription(scopeName);
    
          //class to be used to provision the scope defined above
          var serverProvisioning = new SqlSyncScopeProvisioning();
          serverProvisioning.ObjectSchema = schemaName;
    
          //determine if this scope already exists on the server and if not go ahead and provision
          if (!serverProvisioning.ScopeExists(scopeName, (System.Data.SqlClient.SqlConnection)provider.Connection))
          {
            //add the approrpiate tables to this scope
    
            // todo: SHOULD WORRY ABOUT ORDER HERE
            foreach (var tableName in tablesToCreateScopeWith)
            {
              scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable(tableName,
                                                 (SqlConnection)
                                                 provider.Connection));
            }
            //note that it is important to call this after the tables have been added to the scope
            serverProvisioning.PopulateFromScopeDescription(scopeDesc);
    
            serverProvisioning.ObjectSchema = schemaName; // possible bug according Kyle?
    
            //indicate that the base table already exists and does not need to be created
            serverProvisioning.SetCreateTableDefault(DbSyncCreationOption.Skip);
    
            //provision the server
            serverProvisioning.Apply((SqlConnection)provider.Connection);
          }
          provider.ObjectSchema = schemaName;
        }

    Peter Kellner http://peterkellner.net Microsoft MVP • ASPInsider
    Sunday, June 13, 2010 3:46 AM

Answers

  • you should set SetCreateTrackingTableDefault to Skip as well and a couple other SetCreate methods. check out my short post about adding an additional scope here: http://jtabadero.spaces.live.com/blog/cns!BF49A449953D0591!1184.entry

    hth,

    junet

    • Marked as answer by Peter Kellner Sunday, June 13, 2010 2:31 PM
    Sunday, June 13, 2010 5:15 AM
    Moderator
  • hi peter,

    you're correct. you need to make sure you go thru each scope to determine if the table in your current scope is already part of another scope so you can configure Sync Fx to skip creating the other objects (tracking tables, triggers, etc).

    btw, another thing you may want to consider with scopes is that rows may go to another scope (becomes out of scope from the previous scope). For example, you define two scopes: Scope A with filter State=WA and Scope B with filter State=CA, then you have lets say a salesman that used to be in WA but moved to CA. Any client who previously downloaded the salesman under WA will not get a change notification that the salesman moved to CA.

    hth,

    junet

    • Marked as answer by Peter Kellner Sunday, June 13, 2010 2:31 PM
    Sunday, June 13, 2010 1:48 PM
    Moderator

All replies

  • you should set SetCreateTrackingTableDefault to Skip as well and a couple other SetCreate methods. check out my short post about adding an additional scope here: http://jtabadero.spaces.live.com/blog/cns!BF49A449953D0591!1184.entry

    hth,

    junet

    • Marked as answer by Peter Kellner Sunday, June 13, 2010 2:31 PM
    Sunday, June 13, 2010 5:15 AM
    Moderator
  • Thanks June.

    I'm trying to add a new scope in a generic way.  That is, I'm letting the "user" create a new scope.  Does this mean I need to look at all the existing scope's first, see if the tables are covered in previous scopes, then set the "SetCreate"'s accordingly?

    That is, assume I walk up to an existing database catalog that already has scope's defined in it.  Before I define a new scope, do I need to iterate through all existing scopes, see what tables are covered by previous scope definitions, and if a table is covered do all the stuff you have in your post?


    Peter Kellner http://peterkellner.net Microsoft MVP • ASPInsider
    Sunday, June 13, 2010 1:29 PM
  • hi peter,

    you're correct. you need to make sure you go thru each scope to determine if the table in your current scope is already part of another scope so you can configure Sync Fx to skip creating the other objects (tracking tables, triggers, etc).

    btw, another thing you may want to consider with scopes is that rows may go to another scope (becomes out of scope from the previous scope). For example, you define two scopes: Scope A with filter State=WA and Scope B with filter State=CA, then you have lets say a salesman that used to be in WA but moved to CA. Any client who previously downloaded the salesman under WA will not get a change notification that the salesman moved to CA.

    hth,

    junet

    • Marked as answer by Peter Kellner Sunday, June 13, 2010 2:31 PM
    Sunday, June 13, 2010 1:48 PM
    Moderator
  • that's an interesting point about the salesman moving to a different "filter".  How would you solve that problem in real life? 

    I think a more common scenario would be if you are tracking state.  That is, if you had support calls that you were tracking escalations of. Say you have a person who tracks just high priority calls.  It seems that when a call is moved from normal priority to high priority, others who are syncing would not know it was moved.

    Also, are there any MS docs (besides your blog of course) that discuss this scope issue in more detail?

    Thanks again.

     


    Peter Kellner http://peterkellner.net Microsoft MVP • ASPInsider
    Sunday, June 13, 2010 2:31 PM
  • June,

    I'm a little confused how to skip for some tables and create for others following your blog post http://jtabadero.spaces.live.com/blog/cns!BF49A449953D0591!1184.entry?sa=390155080.  In your example, you skip all the tables at once when you do your provisioning.  In my case, I may have tablea which was in another scope, and table b which was not and I now want to create a scope that is tablea and table b.

    Thanks


    Peter Kellner http://peterkellner.net Microsoft MVP • ASPInsider
    Sunday, June 13, 2010 10:26 PM
  • hi peter,

    the blog post simply demonstrates how to add an additional scope and the assumption is that you have previously provisioned a scope using the same base tables, as such, i simply skip everything else.

    my mistake, i didnt explain too well that the SetCreate methods is defined for the scope itself and not for each table.

    so in your case, if table A is already part of another scope and you want it to be part of another scope with Table B which is not part of any other scope, you cannot selectively specify skipping creating the sync objects for Table A and create objects  for Table B only. That is if you specify Skip, skip is applied to all tables in the scope.

    these are some of the quirks of the collaboration providers. Others are: you can't delete a scope, you can't modify a scope, the scope filter is static and hardcoded in the SelectChanges stored proc.

    perhaps, for your scenario, you might get more flexibility using the offline providers. In the offline provider, you can dynamically add/remove tables from the SyncGroups table collection and at the same time apply dynamic filters as well.

     

    Monday, June 14, 2010 12:46 AM
    Moderator
  • hi peter,

    the other option you may want to explore assuming you dont mind having duplicate sync objects for a table is to specify a different ObjectPrefix for each scope definition and not specifying Skip. I havent tested it, but im assuming it will get rid of those exceptions that the sync objects has been created already.

    Monday, June 14, 2010 12:59 AM
    Moderator
  • Will the prefix do everything including stored procedures and triggers?  can you show me the line of code that adds the prefix?

    Thanks,


    Peter Kellner http://peterkellner.net Microsoft MVP • ASPInsider
    Monday, June 14, 2010 1:28 AM
  • serverProvisioning.ObjectPrefix = "scopename_prefix";
    Monday, June 14, 2010 1:58 AM
    Moderator