none
Sync provisioning not working RRS feed

  • Question

  • I have an existing SQL Compact database running on a single users machine. This client recently came to me and wanted to put the data in the cloud so he can run the Compact database on more machines and sync them up. So, I created a SQL 2008 Express database on this clients existing hosting provider. I ran this code to provision the new database and prepare it for sync:

          using (SqlCeConnection sqlCeConn = new SqlCeConnection(ConnectionString()))
          {
            using (SqlConnection sqlExpressConnection = new SqlConnection(ConnectionCloudString()))
            {
              DbSyncScopeDescription desc = new DbSyncScopeDescription(scopeName);
             
              foreach (string table in tableNames)
              {
                desc.Tables.Add(SqlCeSyncDescriptionBuilder.GetDescriptionForTable(table, sqlCeConn));
              }
          
              //Check and sync enable the Compact database
              SqlCeSyncScopeProvisioning ceScope = new SqlCeSyncScopeProvisioning(sqlCeConn, desc);
              if (!ceScope.ScopeExists(scopeName))
                ceScope.Apply();
    
              SqlSyncScopeProvisioning expressScope = new SqlSyncScopeProvisioning(sqlExpressConnection, desc);
              if (!expressScope.ScopeExists(scopeName))
              {
                expressScope.Apply();
              }
            }        
          }
    

    I do not think my SQL Express database is getting provisioned correctly. But, I am not getting any errors. When the expressScope.Apply() runs it succeeds. And, my schema and tables seem to get applied correctly to the sql express database. I even see all the new tables and triggers that the Sync Framework creates on the both databases.

    However, if I run this code again it always returns false on if(!expressScope.ScopeExists(scopeName). It only does this on the express database. The original CE database seems to understand the scope already was created, but the express doesn't. So, the Apply always gets called again on the Express database and it fails saying the tables, triggers, etc... already exist.

    So, I figured I would just comment this code out so I could run the code for my SyncOrchestrator. However, when I run the syncOrchestrator.Synchronize() I get a message saying the database is "not" provisioned.

    Thus, I am really confused how all those tables got created by the Sync Framework in the Express database, but somehow it thinks the Sync was not provisioned correctly. It is definetly not a connection string or permission issue because it created the tables in the database in the first place. This is very weird, please help!

    Sunday, July 24, 2011 6:26 PM

Answers

  • try setting the ObjectSchema to match the user name under which the objects are being created. Better if you can just set the schema to dbo. Sql Ce doesnt support schemas so you dont get that behaviour.

    try something similar to this

    SqlSyncScopeProvisioning expressScope = new SqlSyncScopeProvisioning(sqlExpressConnection, desc);
    expressScope.ObjectSchema = "User1";
         if (!expressScope.ScopeExists(scopeName))
         {
          expressScope.Apply();
         }
    
    


     

    • Marked as answer by Greg Galipeau Tuesday, July 26, 2011 11:16 AM
    Tuesday, July 26, 2011 1:10 AM
    Moderator

All replies

  • can you check the scope_info table if the scope is there? also can you verify under what schema is the sync fx objects being created?
    Monday, July 25, 2011 3:00 AM
    Moderator
  • Yes, the scope is in the scope_info table. I'm not sure where to find the schema for the sync fx. I looked at the schema_info table and I saw schema_major_version at 2 and schema_minor_version at 1. Is that what you are referring to?
    Monday, July 25, 2011 4:46 PM
  • just wanted to verify if the sync objects were created under dbo or some other name. if theyre created under dbo, you should see the objects listed prefixed with 'dbo.'.
    Monday, July 25, 2011 4:53 PM
    Moderator
  • What do you mean by sync objects? I see the sync tables, but I am not sure what the sync objects are or where to look at them. Is there a place in the database to find that information?
    Monday, July 25, 2011 4:55 PM
  • by sync objects am referring to the tables, stored procedurs, triggers, etc.., created by the provisioning
    Monday, July 25, 2011 11:54 PM
    Moderator
  • Oh, ok. They were created with username in the connection string I used to create them. So, for example: If my username is User1. And, lets say I have a table named Address. Then the table was created as User1.Address.

    I don't know if this helps the issue, but I don't have integrated authentication into this database. I had to use a username and password in my connection string because the database is at a hosting provider.

    Also, the original database (i.e.: the compact database) were not created under any name. So, the Address table in the compact database is just Address.

    Hopefully this information helps you a little. I'm stumped.

    Tuesday, July 26, 2011 12:04 AM
  • try setting the ObjectSchema to match the user name under which the objects are being created. Better if you can just set the schema to dbo. Sql Ce doesnt support schemas so you dont get that behaviour.

    try something similar to this

    SqlSyncScopeProvisioning expressScope = new SqlSyncScopeProvisioning(sqlExpressConnection, desc);
    expressScope.ObjectSchema = "User1";
         if (!expressScope.ScopeExists(scopeName))
         {
          expressScope.Apply();
         }
    
    


     

    • Marked as answer by Greg Galipeau Tuesday, July 26, 2011 11:16 AM
    Tuesday, July 26, 2011 1:10 AM
    Moderator
  • Thank you so much June. I didn't see that in the documentation and I don't think I would have found it without your help.
    Tuesday, July 26, 2011 11:16 AM