CODEWITHSUNDEEP
sqlsql-server
johnny tan
johnny tan

Reputation: 19

SQL - Invalid object name

Here are the steps to understand what happened (I am using Microsoft SQL Server Management Studio 2017):

  1. I imported an Excel file as a table
  2. I renamed my table to dbo.export
  3. To test, I created a new query and simply wrote the statement SELECT * FROM dbo.export - it runs fine
  4. I save my query
  5. I close SSMS
  6. I reopen SSMS and open my query
  7. When I try to execute my query now, it gives me the error:

Msg 208, Level 16, State 1, Line 1. Invalid object name 'dbo.export'

I made no changes to anything. My code now suddenly doesn't work. Is SQL Server somehow changing the object type of my table? I really have no idea what's the issue since I changed absolutely nothing.

Upvotes: 1

Views: 15588

Answers (6)

poc
poc

Reputation: 11

in 2024, I also experienced the same problem. I tried all the methods mentioned but all failed. Then I realized that I could use MSSM to figure out the problem. I connected to the remote database and then chose the 'select top 1000 rows'. The script showed as select top 1000 * from databasename.schemaname.tablename.

I don't know why people didn't mention this previously. The correct way of executing a query mentioning a table is always as below no matter what tools do you use:

use databasename; # if you didn't use a database in the first place
select * from schemaname.tablename;
go

Upvotes: 0

vickyhills
vickyhills

Reputation: 1

Just solved mine

  1. include the databasename and the tablename ie select * from databasename.dbo.tablename

The above should solve the problem 👌

Upvotes: 0

kkica
kkica

Reputation: 4104

You are probably in the master database. Either select your database where it says "master" in SSMS: enter image description here

or use the name of the database in your query like this:

DBNAME.dbo.export

or in the beginning of the query, type 

USE DBNAME;

And then go on with your query SELECT * FROM export;

Upvotes: 4

Ahmad Gozin
Ahmad Gozin

Reputation: 23

When you close your query the default database will change to the master database.

Every time you open a query, you should select the database. Choose one of these ways.

1) Select your database from the combo-box on toolbar on top of the SSMS menu.

2) Just write this command before your T-SQL statement.

 USE 'place your database name'

Upvotes: 1

Aiham Al-Zubaidy
Aiham Al-Zubaidy

Reputation: 21

You can use the database in the first line then use your selection query:

USE databaseName;

SELECT * FROM export;

Upvotes: 2

Hooman Bahreini
Hooman Bahreini

Reputation: 15579

You must have created a Database for importing the excelsheet. Make sure you select the same database when you run the query:

enter image description here

In the example above, AdventureWorks2012 is the selected Database.

Alternatively you can include the DB name in your query:

SELECT * FROM myDbName.dbo.export

Upvotes: 1

Related Questions