locked
SyncSvcUtil: Fails to provision database on named instance RRS feed

  • Question

  • I have discovered another unexpected behaviour in SyncSvcUtil (at least it doesnt fit the documented behaviour):

    We are sharing a sync config file among colleagues and this file contains two different TargetDatabase definitions, one targeting a default instance and one targeting a named instance. When trying to provision the named instance the generated stored procedures are added to the wrong schema.

    Since we where omitting the SchemaName attribute in the SyncScope configuration element we expected them to be created in the dbo schema as this is the documented behaviour (and it used to work well in the 3.0 CTP). Instead, they are created in a schema that matches the name of the instance. As soon as we explictly provide the SchemaName attribute, things work as they should.

    Example:

     

     <
    SyncConfiguration
    >
    
    
     <
    SyncScopes
    >
    
      <
    SyncScope
     Name
    =
    "
    DefaultScope
    "
     IsTemplateScope
    =
    "
    true
    "
    >
    
    ...
      </
    SyncScope
    >
    
     </
    SyncScopes
    >
    
    
     <
    Databases
    >
    
      <
    TargetDatabase
     Name
    =
    "
    MyDatabaseName
    "
     DbServer
    =
    "
    .
    "
     DbName
    =
    "
    MyDatabase
    "
     UserName
    =
    "
    MyUser
    "
    
        Password
    =
    "
    changeme
    "
     />
    
      <
    TargetDatabase
     Name
    =
    "
    MyInstanceName:MyDatabaseName
    "
     DbServer
    =
    "
    .\MyInstanceName
    "
     DbName
    =
    "
    MyDatabaseName
    "
     UserName
    =
    "
    MyUser
    "
    
        Password
    =
    "
    changeme
    "
     />
    
     </
    Databases
    >
    
    
     </
    SyncConfiguration
    >
    
    
    

     

    The command issued to provision is:

     

    SyncSvcUtil.exe /verbose /mode:provision /scopename:DefaultScope /scopeconfig:MyConfig.xml /database:MyInstanceName:MyDatabaseName

     

    As a result the stored procedures are created in a database schema called MyInstanceName.

     

    • Edited by M.Bi Friday, October 29, 2010 7:48 AM
    Thursday, October 28, 2010 12:44 PM

Answers

  • Hi M.Bi,

    The provisioning logic creates object in the default schema which may be dbo or something that is configured for the current login session. Can you please get us the following details:

    Is there a schema name with the name <MyInstanceName> in your database? SELECT * FROM sys.schemas;

    Can you open a query window with the sql login that you use and run the following command. This should show a mapping of sql logins to the corresponding default schema

    SELECT name, default_schema_name FROM sys.database_principals WHERE type = 'S'


    SDE, Sync Framework - http://www.giyer.com
    • Marked as answer by mjayaram Monday, November 1, 2010 6:44 PM
    Friday, October 29, 2010 6:04 PM

All replies

  • I will create a bug to fix this. Meanwhile - what if you use this:

    <TargetDatabase Name="MyDatabaseName" DbServer=".\MyInstanceName" DbName="MyDatabaseName" UserName="MyUser"
           Password="changeme" />

    This should create tracking objects in the dbo schema.

    Thursday, October 28, 2010 5:35 PM
  • The problem remains after removing the colon from TargetDatabase Name. Obviously it's the named instance which causes the confusion. As a workaround we will explicitly provide the SchemaName attribute for our SyncScope elements.

    Friday, October 29, 2010 7:27 AM
  • Hi M.Bi,

    The provisioning logic creates object in the default schema which may be dbo or something that is configured for the current login session. Can you please get us the following details:

    Is there a schema name with the name <MyInstanceName> in your database? SELECT * FROM sys.schemas;

    Can you open a query window with the sql login that you use and run the following command. This should show a mapping of sql logins to the corresponding default schema

    SELECT name, default_schema_name FROM sys.database_principals WHERE type = 'S'


    SDE, Sync Framework - http://www.giyer.com
    • Marked as answer by mjayaram Monday, November 1, 2010 6:44 PM
    Friday, October 29, 2010 6:04 PM
  • Oh, very interesting :-)

    We have compared both the named instance and the default instance and on both servers the situation is the same: we do have a schema called "MyCompany" and it is set as the "MyUser"'s default schema. For the named instance this is also the name of the instance (refered to as "MyInstance" in my previous posts).

    With this information in mind it now seems to me as if the behaviour seen when provisioning the named instance is not faulty but instead fully matches the defined behaviour (database objectes are beeing created in the "MyCompany" schema wich is the configured default schema for "MyUser" on the "MyCompany" instance). Instead, provisioning the default instance does not behave correctly, because the database objects are beeing created in the dbo schema regardless of the user's configured default schema.

    I am sorry for starting this thread with a wrong interpretation of the problem. I just were not aware of the "MyCompany" default schema configuration and comming from SyncFX 3 CTP which created all objects in dbo schema all the time i was just expecting that.

     

    Monday, November 1, 2010 8:29 AM