CODEWITHSUNDEEP
sql-server
wootscootinboogie
wootscootinboogie

Reputation: 8695

`Invalid object name` unless I specify database name in SQL Server 2008 queries

Up until today I've been able to run queries without using the [databaseName].[dbo].[fieldName] syntax. And all of a sudden, if I use select * from myTable I get an error for an invalid object. I can't possibly think of something that happened between shutting down my PC yesterday and today. Anyone know anything about this?

Msg 208, Level, 16, State 1 Line 1
Invalid object name 'mytable'

It's only been since today that I have to include the database name in the query. There are no other connections open and no other users of this instance of SQL Server.

Upvotes: 0

Views: 15438

Answers (1)

StuartLC
StuartLC

Reputation: 107237

I think you mean a query of the form select [fieldName] from [databaseName].[dbo].[mytable]

Here are some possible things to look out for:

  • Make sure that you are in the correct database context / catalogue (i.e. use [databasename], or select the correct database from the Available Databases drop down in SSMS)

enter image description here

  • Ensure that if you have a case sensitive collation on your database that the object names in your query match the exact case.
  • Check that the default schema for your user hasn't changed on this database. Although the default schema is usually [dbo], it can be changed.

Edit : More ideas:

  • Do SELECT DB_NAME() to see what the current database name is.
  • Check to see if someone has dropped the table or view entirely, e.g. from the target database, run:

Select * from sysobjects where name = 'myobject' 
OR
Select * from sys.tables where name = 'mytable' 
OR
Select * from INFORMATION_SCHEMA.TABLES

Upvotes: 5

Related Questions