Unable to Run Query, dbo.tablename invalid object name?

Question

I am trying to build a dynamic query that will insert or update a record based on row exists in db, if yes i will update a bunch of records & sub records depending on @ObjectID.

Here is my query:

DECLARE @ObjectID BIGINT = 0;
SET @ObjectID = 0;

IF NOT EXISTS (SELECT ID 
               FROM dbo.ResortInfo 
               WHERE dbo.ResortInfo.resortCode = N'PYI')
BEGIN
    INSERT INTO dbo.ResortInfo (columns) 
    VALUES (colvalues)

    SET @ObjectID = SCOPE_IDENTITY()

    PRINT @ObjectID
END
ELSE
BEGIN
    PRINT 'Already exists' -- update query will replace here
END

The query runs ok without the declare part, but when I add

DECLARE @ObjectID BIGINT = 0;
SET @ObjectID = 0;

I get the following error :

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

That usually means that you don't actually have a table named `dbo.ResortInfo`. — Tab Alleman, Aug 21 '19 at 18:47
You should get the same error if you run `select * from dbo.ResortInfo` — S3S, Aug 21 '19 at 18:50
after both your comments, i checked the SSMS again and the db master was selected in query runner now i feels like a fool — Alok, Aug 21 '19 at 19:01
and here i was headbutting with this issue since last 4 hours — Alok, Aug 21 '19 at 19:02

score 2 · Accepted Answer · answered Aug 21 '19 at 19:02

2

I would double check that you have the correct db selected. I currently have master selected, but your table may live in another.

In your stored procedure you could add this at the top to ensure you are using the correct one.

USE [<your db name here>]

answered Aug 21 '19 at 19:02

Wilco

374
1
11

yup, that was it i didnt noticed it when i was using the new query button from live db it defaulted to master database lol – Alok Aug 21 '19 at 19:03

Unable to Run Query, dbo.tablename invalid object name?

1 Answers1