Post reply

Can''t find own tables / Implicit schema name not considered

  • May 24, 2007 at 2:19 pm

    #174448

    Hello there,

    I have a strange problem here. Implicit schema names are not evaluated correctly.

    Even though I have created the table like

    CREATE TABLE TABLE1 (COL1 INT);

    when being connected as user 'user1', I cannot access that table from my Java test program. According to the SQL Server Management Studio of SQL Server 2005 my table is in the schema 'user1'.

    This is my program:

    import java.sql.*;
public class SchemaTest {
    public static void main(String[] args) throws Exception {
        Class.forName("com.ddtek.jdbc.sqlserver.SQLServerDriver");
        Connection conn = DriverManager.getConnection(
            "jdbc:datadirect:sqlserver://localhost:1433;DatabaseName=database1",
            "user1", "password1");
        Statement stmt = conn.createStatement();
        ResultSet result = stmt.executeQuery("SELECT COL1 FROM TABLE1");
        while(result.next()) {
            System.out.println(result.getInt(1));
        }
        result.close();
        stmt.close();
        conn.close();
    }
}

    I connect with the very same user, but the result is an exception:

    Exception in thread "main" java.sql.SQLException: [DataDirect][SQLServer JDBC Driver]
[SQLServer]Invalid object name 'TABLE1'.

    If I specify the schema name explicitly, it works fine:

            ...
        ResultSet result = stmt.executeQuery("SELECT COL1 FROM user1.TABLE1");
        ...

    Does anyone know, how I can use schemata implicitly?

    What is the schema name that SQL Server 2005 looks for? Is it 'dbo'?

    Any help is highly appreciated.

    Kind regards,

    slowjoe

  • May 24, 2007 at 2:36 pm

    #708795

    I just noticed that the default schema for user 'user1' is 'dbo'. There is a schema 'user1', too. I tried to change the default schema to 'user1', but after I committed the changes (click on 'OK' in panel 'Database User - user1'), they are not getting persisted.

    When I re-open the panel, the default schema is 'dbo' again...

    Then I tried to change the owner of the schema 'user1' from 'dbo' to user 'user1'. This worked well and even got persisted. However, this does not change anything with regard to my 'Invalid object name' issue.

    I am desperate...

  • May 24, 2007 at 2:48 pm

    #708798

    Now, I dropped the database (including user 'user1').

    Then I recreated the database and added a schema named 'user1'.

    Thereafter, I recreated user 'user1' and specified in the 'Database User - New' dialog that the default schema of that new user is 'user1'. Then I committed the settings. However, when I look into the properties of this new user 'user1', then his default schema is 'dbo' again...

    What is wrong with me or SQL Server 2005... Any ideas?

  • May 25, 2007 at 12:53 am

    #708867

    Dunno about the problem changing the default schema, try using T-SQL rather than the GUI. ALTER USER ...

    It is recomended practive to always specify the schema name when refering to a table - select ... from dbo.Table1 rather than select ... from table1. Not specifying the schema potentially means more wik for SQL to find the table and has the potential to prevent execution plan reuse.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 25, 2007 at 12:54 am

    #708868

    I found out, what is the issue.

    First, the table has not been created like

    CREATE TABLE TABLE1 (COL1 INT);

    but like this:

    CREATE TABLE user1.TABLE1 (COL1 INT);

    This explains, why it has been put in schema 'user1'.

    I also fixed the 'default schema issue'. Somehow, it does not work to modify the default schema in this place:

    'localhost (SQL Server 9.0.3042 - sa)'
 > Databases
  > 'database1'
   > Security
    > Users
     > 'user1'
      > Properties
       > General
        > Default schema

    Instead, I was able to modify the default schema in this place:

    'localhost (SQL Server 9.0.3042 - sa)'
 > Security
  > Logins
   > 'user1'
    > Properties
     > User Mapping
      > Users mapped to this login
       > Default Schema in the row of 'database1'

    I really think, this is a bug. What do you think?

    Regards,

    slowjoe

  • May 25, 2007 at 1:25 am

    #708875

    Hello Gail,

    thanks for your reply.

    I am completely aware of that issue. The example I gave is just a simplification of the real problem. I have to work with an application that does not define schema names. However, I'm not allowed to modify the sources of that application.

    Regards,

    slowjoe

  • May 25, 2007 at 1:39 am

    #708879

    Ah. In that case, I would suggest remove all schemas other than dbo and make sure that all users have the dbo default schema. That should remove any possibility for problems

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply