Problem with MS SQL database with multiple schemas

2 posts, 0 answers
  1. Makoto
    Makoto avatar
    16 posts
    Member since:
    Nov 2006

    Posted 25 Mar 2010 Link to this post

    I have a database that is using several schemas.  I was able to generate the classes with Reverse Mapping just fine, and also see the Schema name show up in the mappings.

    For example, I have a table called AccountBase in a schema called ClientSetup. Here's the XML mapping:
    <class name="AccountBase"
                <extension key="db-do-not-create-table" value="true" /> 
                <extension key="db-table-name" value="ClientSetup.AccountBase" /> 

    However, when I query it by using the following:


    I get the following error:
    Telerik.OpenAccess.RT.sql.SQLException: Invalid object name 'AccountBase'

    In the exception, the SQL that it's using is this (shortened for brevity):
    SELECT a.[ID] AS COL1, ......, a.[WebSite] AS COL35 FROM [AccountBase] 

    It should have generated SQL that said FROM [ClientSetup].[AccountBase] instead.

    Also, in our project, we have several different customers, and each customer can have their own separate database, and we store the connection strings for each customer in the database.  We have a common database and several customer databases.  I've had to make a custom ObjectScopeProvider (called CustomerScopeProvider) to handle this, and hopefully it is correct. I'm using the generated ObjectScopeProver class to handle all the Common database tasks.

    Here's the code that I use to get an instance of the CustomerScopeProvider:

     public static IObjectScope GetPerRequestScope(HttpContextBase context, int customerId) 
                IObjectScope commonScope = ObjectScopeProvider.GetPerRequestScope(context); 
                var customer = commonScope.Extent<Customer>().Single(c => c.Id == customerId); 
                string connection = string.Format("Data Source={0};Initial Catalog={1};User Id=user;Password=password", customer.DBServer, customer.DBName); 
                string key = HttpContext.Current.GetHashCode().ToString("x") + Thread.CurrentContext.ContextID.ToString() + customerId.ToString(); 
                IObjectScope scope; 
                if (context == null
                    scope = Database.Get(connection, commonScope.Database.BackendConfiguration, commonScope.Database.MetaData).GetObjectScope(); 
                    scope = (IObjectScope)context.Items[key]; 
                    if (scope == null
                        scope = Database.Get(connection, commonScope.Database.BackendConfiguration, commonScope.Database.MetaData).GetObjectScope(); 
                        context.Items[key] = scope; 
                return scope; 

    Is there something that I'm doing wrong that would cause it to not include the schema name in the query?

  2. Serge
    Serge avatar
    375 posts

    Posted 26 Mar 2010 Link to this post

    Hello Makoto,

    First I want to ask you if all your databases contain the same schemata. Essentially what you are doing is getting an scope to the main database and then passing its backend configuration and metadata to Database.Get() in order to get a new scope (if there are any differences in the metadata you are likely to get in trouble).

    What I will suggest is to use the Database.Get() overload that has only one argument, the connection id. Also can you try executing your query with the default scope and get back to us.

    Best wishes,
    the Telerik team

    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
  3. DevCraft banner
Back to Top