locked
Error when using a specified or dynamically created database schema. RRS feed

  • Question

  • First let me appologize for the redundant thread, but after working on the issue for some time, my initial problem/question is actually invalid. Basically my issue is this, if I use the dbo schema, everything works as expected. If I create one and then specify it I get an error. If I let the sync framework dynamically create the schema I get an error. Please note too that it looks like everything gets created; triggers, tracking tables, types, procedures, scope related tables (all populated) etc. I can not for the life of me get past this. Does anyone have any ideas? The error I get as a result is as follows:

    ERROR : The current operation could not be completed because the database is not provisioned for sync or you not have permissions to the sync configuration tables.

     

    Here is my application trace:

    INFO   , Magazine.Bridge.Host, 7, 10/20/2010 13:25:27:558, --- END Provisioning Table '[MagazineLanguage]' ---
    INFO   , Magazine.Bridge.Host, 7, 10/20/2010 13:25:27:558, Updating Scope Config Status of Scope '92ab9d98-4bdf-42bc-864b-3818271b1712' to 'C'
    VERBOSE, Magazine.Bridge.Host, 7, 10/20/2010 13:25:27:558,    Executing Command: UPDATE [scope_config] SET scope_status = 'C'
    WHERE [config_id] = '92ab9d98-4bdf-42bc-864b-3818271b1712';
    INFO   , Magazine.Bridge.Host, 7, 10/20/2010 13:25:27:558, --- END Provisioning Scope 'languageservice_tables' on Database 'LanguageService' ---
    INFO   , Magazine.Bridge.Host, 12, 10/20/2010 13:25:27:636,    BeginSession() called on Provider SqlSyncProvider, Microsoft.Synchronization.Data.SqlServer, Version=3.1.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
    VERBOSE, Magazine.Bridge.Host, 12, 10/20/2010 13:25:27:636, Connecting using string: Data Source=sqllab;Initial Catalog=LanguageService;Integrated Security=True;Connect Timeout=1
    ERROR  , Magazine.Bridge.Host, 12, 10/20/2010 13:25:27:652, The current operation could not be completed because the database is not provisioned for sync or you not have permissions to the sync configuration tables. Exception: Microsoft.Synchronization.Data.DbNotProvisionedException: The current operation could not be completed because the database is not provisioned for sync or you not have permissions to the sync configuration tables.
       at Microsoft.Synchronization.Data.SqlServer.SqlManagementUtils.VerifyRuntimeVersionExceedsSchema(SqlConnection connection, String objectPrefix, String objectSchema, Boolean throwWhenNotProvisioned) 

    I am using Sql Server 2005. This happens on my local machine as well as using a remote machine and a combination of both.

    Please let me know if you have any ideas. Thank you for your patience with me as I try to solve this issue. 

    Thursday, October 21, 2010 6:16 PM

Answers

  • As mentioned in my other thread after extensive debugging I decided to put a stop on all my "new SqlSynProvider()" calls and found that one out of the bunch was failing to call the schema, actually it was overwriting my original instantiation with blank schema settings. I am going to eliminate the duplicates and clean it all up. I appreciate your help and time. I am just glad I got this fixed. I have learned a lot from this and would have never found it without the sql trace that was suggested etc, which made me learn some new things about debugging. Thanks again for everyones patience.
    • Marked as answer by KryptonianSon Friday, October 22, 2010 12:58 PM
    Friday, October 22, 2010 12:58 PM

All replies

  • Here is the scope that gets created. I list this because when I rerun the sync, it wants to recreate everything as if it is not there even though it is.

    <SqlSyncProviderScopeConfiguration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" IsTemplate="false">
      <Adapter Name="[Employees]" GlobalName="[Employees]" TrackingTable="[SyncSchema2].[Employees_tracking]" SelChngProc="[Employees_selectchanges]" SelRowProc="[SyncSchema2].[Employees_selectrow]" InsProc="[SyncSchema2].[Employees_insert]" UpdProc="[SyncSchema2].[Employees_update]" DelProc="[SyncSchema2].[Employees_delete]" InsMetaProc="[SyncSchema2].[Employees_insertmetadata]" UpdMetaProc="[SyncSchema2].[Employees_updatemetadata]" DelMetaProc="[SyncSchema2].[Employees_deletemetadata]" BulkTableType="[SyncSchema2].[Employees_BulkType]" BulkInsProc="[SyncSchema2].[Employees_bulkinsert]" BulkUpdProc="[SyncSchema2].[Employees_bulkupdate]" BulkDelProc="[SyncSchema2].[Employees_bulkdelete]" InsTrig="[Employees_insert_trigger]" UpdTrig="[Employees_update_trigger]" DelTrig="[Employees_delete_trigger]">
        <Col name="EmployeeID" type="int" param="@P_1" pk="true" />
        <Col name="LastName" type="nvarchar" size="20" param="@P_2" />
        <Col name="FirstName" type="nvarchar" size="10" param="@P_3" />
        <Col name="Title" type="nvarchar" size="30" null="true" param="@P_4" />
        <Col name="TitleOfCourtesy" type="nvarchar" size="25" null="true" param="@P_5" />
        <Col name="BirthDate" type="datetime" null="true" param="@P_6" />
        <Col name="HireDate" type="datetime" null="true" param="@P_7" />
        <Col name="Address" type="nvarchar" size="60" null="true" param="@P_8" />
        <Col name="City" type="nvarchar" size="15" null="true" param="@P_9" />
        <Col name="Region" type="nvarchar" size="15" null="true" param="@P_10" />
        <Col name="PostalCode" type="nvarchar" size="10" null="true" param="@P_11" />
        <Col name="Country" type="nvarchar" size="15" null="true" param="@P_12" />
        <Col name="HomePhone" type="nvarchar" size="24" null="true" param="@P_13" />
        <Col name="Extension" type="nvarchar" size="4" null="true" param="@P_14" />
        <Col name="Photo" type="image" null="true" param="@P_15" />
        <Col name="Notes" type="ntext" null="true" param="@P_16" />
        <Col name="ReportsTo" type="int" null="true" param="@P_17" />
        <Col name="PhotoPath" type="nvarchar" size="255" null="true" param="@P_18" />
        <FilterClause>[side].[Region] = 'WA'</FilterClause>
        <FilterCol>Region</FilterCol>
      </Adapter>
    </SqlSyncProviderScopeConfiguration>

    Thursday, October 21, 2010 6:35 PM
  • As mentioned in my other thread after extensive debugging I decided to put a stop on all my "new SqlSynProvider()" calls and found that one out of the bunch was failing to call the schema, actually it was overwriting my original instantiation with blank schema settings. I am going to eliminate the duplicates and clean it all up. I appreciate your help and time. I am just glad I got this fixed. I have learned a lot from this and would have never found it without the sql trace that was suggested etc, which made me learn some new things about debugging. Thanks again for everyones patience.
    • Marked as answer by KryptonianSon Friday, October 22, 2010 12:58 PM
    Friday, October 22, 2010 12:58 PM
  • Yes. I was about to suggest looking at ObjectSchema being passed to provider, as someone also experienced same error and suggested similar solution.

    I am glad you found the issue and got it fixed.

    Patrick

    Friday, October 22, 2010 6:20 PM
  • In connection with this, perhaps you can tell me or help me figure out why all the stored procedures that get created/provisioned have the correct schema (example: SyncSchema) I specify, however the _selectchanges sp uses the dbo schema. If I specify a different schema as the default schema for my login user other than dbo, it can create everything just fine, but when deprovisioning it cannot remove the _selectchanges stored procedures. I have tried to fix this all day long. Any ideas?

    Friday, October 22, 2010 6:42 PM
  • Looks like you might have found a bug we recently fixed. The bug was that if you used object schema, select changes proc incorrectly was created under dbo. We fixed this. With respect to deprovisioning, did you set the object schema on the deprovisioning object? it should deprovision everything creted under the specified schema.

    Patrick

    Friday, October 22, 2010 9:09 PM
  • Patrick,

    It removes everything just fine, unless I have a different default schema set to my user and have specified the same object schema to my provider. Deprovisioning in this case removes everything except for the select changes proc.

    Again if if the system used the dbo schema to create the select changes proc, then deprovisioning all procs works. If the system uses the default/specified schema in conjunction, deprovisioning that one proc does not work.

    Is there an ETA when the fix will be released to the public?

    Monday, October 25, 2010 12:23 PM
  • I would also like to add that when using ObjectPrefix, the prefix does not get added to the _selectchanges procs. So there is more confusion there.
    Monday, October 25, 2010 6:09 PM
  • Hi,

    It is a known bug in SyncFx 2.1 and will be fixed in next release. One workaround is to create the provision script with SqlSyncProvision.Script() and modify this TSQL script before executing it to complete provision. Please let us know if you are just trying SyncFx 2.1 features or are building a real production application now.

    Thanks,
    Dong


    This posting is provided AS IS with no warranties, and confers no rights.
    Monday, October 25, 2010 7:29 PM
  • I am building a real production application now, but if this is something that will be released in the next month or two I can live with it during this phase of development. An ETA would be helpful in making a decision on if I should go through the trouble of a workaround or not.

    Thanks Dong

    Monday, October 25, 2010 8:36 PM
  • I added the Schema “Sync” to the Server Database and set the owner to dbo

    I have set up a Filtered Template and applied it to the SQL2008 R2 Server works fine.

    After the code below is run it appears to set up the Server Correctly

     // Step 1 for Parameter-based Filter
                // Create a scope named "filtered_candidate_template", and add two tables to the scope.
                // GetDescriptionForTable gets the schema of each table, so that tracking 
                // tables and triggers can be created for that table.
                var scopeDesc = new DbSyncScopeDescription("filtered_candidate_template");
     
                // Set a description of the template.
                scopeDesc.UserComment = "Template for Candidate and CandidateForm tables. Candidate data is filtered by DeviceID parameter.";
     
                //// Definition for tables.
                DbSyncTableDescription candidateDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable("Candidate", _serverConn);
                scopeDesc.Tables.Add(candidateDescription);
                DbSyncTableDescription candidateFormDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable("CandidateForm", _serverConn);
                scopeDesc.Tables.Add(candidateFormDescription);
     
                // Create a provisioning object for "filtered_candidate_template" that can be used to create a template
                // from which filtered synchronization scopes can be created. We specify that
                // all synchronization-related objects should be created in a 
                // database schema named "Sync". If you specify a schema, it must already exist in the database.
                var serverTemplate = new SqlSyncScopeProvisioning(_serverConn, scopeDesc, SqlSyncScopeProvisioningType.Template);
                serverTemplate.ObjectSchema = "Sync";
     
                // Specify the column in the Candidate table to use for filtering data, 
                // and the filtering clause to use against the tracking table.
                // "[side]" is an alias for the tracking table.
                // The DeviceID column that defines the filter is set up as a parameter in this template.
                // An actual customer type will be specified when the synchronization scope is created.
                serverTemplate.Tables["Candidate"].AddFilterColumn("DeviceID");
                serverTemplate.Tables["Candidate"].FilterClause = "[side].[DeviceID] = @deviceID";
                var param = new SqlParameter("@deviceID"SqlDbType.UniqueIdentifier);
                serverTemplate.Tables["Candidate"].FilterParameters.Add(param);
     
                // Create the "filtered_candidate_template" template in the database.
                // This action creates tables and stored procedures in the database, so appropriate database permissions are needed.
                //TextWriter tw = new StreamWriter("Forms.txt");
                //tw.WriteLine(serverTemplate.Script());
                //tw.Close();
     
                //string theSQL = serverTemplate.Script();
                serverTemplate.Apply();

     

     

    Now I applit it with the below code

     var serverProvDevice = new SqlSyncScopeProvisioning(_serverConn);
                serverProvDevice.ObjectSchema = "Sync";
                serverProvDevice.PopulateFromTemplate("FilteredCandidates""filtered_candidate_template");
     
                //[To Do get a Device ID from List]  
                // This is hard coded atm
                var theDevice = new Guid("8a80a4db-4d9f-4c23-aa76-8a9d0f7bdefd");
     
                serverProvDevice.Tables["Candidate"].FilterParameters["@deviceID"].Value = theDevice;
                serverProvDevice.UserComment = "Candidate data includes only Candidates for the device.";
                serverProvDevice.Apply();

     

    The tables in the SQL server DB look like this Sync.Candidate_tracking

     

    Again looks like all is there.

     

    I then so the Client CE Database provisioning

     // Now provision the SQL Server Client Side database with the new filtered scope.
                DbSyncScopeDescription clientSqlDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("FilteredCandidates"null"Sync", _serverConn);
                var clientSqlCeConfig = new SqlCeSyncScopeProvisioning(_clientConn, clientSqlDesc);
                clientSqlCeConfig.ObjectPrefix = "Sync";
                clientSqlCeConfig.SetCreateTableDefault(DbSyncCreationOption.Create);
                clientSqlCeConfig.Apply();
     
    After I call this code I end up with what looks like a correct schema. I see the table in the CE database named Sync_Candidate_tracking . This is because CE does not support Schemas Does this what The tables look like?
     
    So at Think Point I’m thinking ok working like all the samples / Docs / Help files say.
    Now when I try to Sync with the below code
     // After the filtered scope has been defined and the client database is provisioned, the client can be synchronized by creating SqlSyncProvider objects
               // for the filtered scope in the client and server databases, associating the providers with a SyncOrchestrator object, and by calling the Synchronize method.
              
     
               // create the sync orhcestrator
                var syncOrchestrator = new SyncOrchestrator();
     
            
                //// set local provider of orchestrator to a CE sync provider associated with the 
                //// FilteredCandidates in the SyncCompactDB compact client database
                syncOrchestrator.LocalProvider = new SqlCeSyncProvider("FilteredCandidates", _clientConn, "Sync");
                
                // set the remote provider of orchestrator to a server sync provider associated with
                // the FilteredCandidates in the SyncDB server database
                syncOrchestrator.RemoteProvider = new SqlSyncProvider("FilteredCandidates", _serverConn, null"Sync");
     
                // set the direction of sync session to Upload and Download
                //syncOrchestrator.Direction = SyncDirectionOrder.UploadAndDownload;
     
                // execute the synchronization process
                SyncOperationStatistics syncStats = syncOrchestrator.Synchronize();
     
     
    The last call to = syncOrchestrator.Synchronize(); throws the execption
    DbNotProvisionedException The current operation could not be completed because the database is not provisioned for sync or you not have permissions to the sync configuration tables.
     
    I’m trying to debug on VS2010 on Windows 7 64bit. I’m using the 2.1 SDK and I have the x86 and x64 runtimes installed.
     
     private readonly SqlCeConnection _clientConn = new SqlCeConnection(@"Data Source='|DataDirectory|\LocalCeDb.sdf'");
     
    This is the connection string.
     
    Any thoughts would be great.
    Thanks
     
    Tuesday, November 2, 2010 4:01 PM
  • You have got to be kidding me. I was creating the CE Database from Within VS2010 as soon As I created a empty BD from SQL Server Management Studio it all works like a champ!

     

    Note to self No Not use VS2010 to Create a CE database!

    Tuesday, November 2, 2010 4:19 PM