SQL SERVER ODBC ERROR(Invalid object name) but when I add in SQL query mydb.dbo.mytable all works fine

Question

I have an old asp.net 1 project (it works fine on old server, mytable exist in db. Now I am trying to upgrade it to asp.net 4

My connection string is:

<add key="SqlConnection" 
     value="DRIVER={SQL Server};SERVER=bel\SQLEXPRESS;Trusted_connection=yes;DAT­ABASE=mydb;option=3;"/>

I get error

ERROR [42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'mytable'.

OdbcCommand dataCommand = new OdbcCommand("select * from mytable", dataConnection);
dataCommand.CommandTimeout = 900;
OdbcDataReader dataReader = dataCommand.ExecuteReader(CommandBehavior.CloseConnection);

When I wrote SQL as select * from mydb.dbo.mytable all works fine

What I should change in db settings (security, schema, dbo) or in connection string?

Answer 1

My guess, seeing that you're using ODBC, is that your ODBC connection doesn't specify a default database, and so it's using master.

You can either:

• specify the database in your connection string using "Database=myDBname" or "InitialCatalog=myDBname"
• change the default database in your ODBC connection, as shown here in XP/Server 2003