Invalid object name SQL Server 2008 R2 - Stored procedure

50
votes

I am attempting to alter an existing stored procedure with the command

ALTER 'name_of_stored"procedure'

however the name is highlighted in red noting it is an invalid object. I am able to execute the command successfully however am wanting to know why SQL Server 2008 R2 notes it as an error.enter image description here

6
sql-server-2008
You are worrying too much about the red line !! . Just write the procedure as it is and click "Execute" everything will work fine. – Shankar Narayana Damodaran
possible duplicate of Could not find stored procedure in SQL Server 2008 however am able to execute it – Aaron Bertrand
@Aaron Bertrand - I didn't realise it would be treated as a duplicate as I thought it was a different problem since it was displaying the tool tip 'invalid object name'. If you think it is a duplicate, please delete the post. I apologize. – PeanutsMonkey
I think it's the exact same cause, and you already have a resolution, yes? Anyway, I can't delete the post, I can only suggest it be closed as a duplicate. If you want the information to live on, maybe take the additional symptom and add it as further information to the other question. – Aaron Bertrand
@Aaron Bertrand - No worries. There is no additional symptom and yes I now have a resolution. – PeanutsMonkey

6 Answers

112
votes

It's SSMS, not SQL Server. If it's an error against the database engine you'll know it (it wouldn't execute the command successfully).


Solution

You need to refresh the Intellisense cache (Ctrl + Shift + R). Or you can go to Edit -> IntelliSense -> Refresh Local Cache.

10
votes

Create a stored procedure by using CREATE PROCEDURE, not ALTER PROCEDURE.

6
votes

Intellisense doesn't refresh as quickly as you create new objects. You need to refresh the cache (Ctrl + Shift + R). Or, maybe, don't rely on Intellisense and only worry if the execution fails.

0
votes

I had the same issue, the problem was that I had a select statement from stored proc who's name was similar to table's name, so instead of 

  select * from Table_Test

I was writing this in the stored procedure

  select * from Proc_Test --selecting from Stored proc, yikes!!

maybe this helps

0
votes

I had the same problem and saw an incredible number of possible answers, some quite exotic. Unfortunately I didn't see this page before finding what worked for me. I thought I would add this even though I believe it serves the same end as refreshing Intellisense. I knew I had done my work right because the stored procedure worked correctly in spite of the error so I finally decided it was not a 'real' error.

I exited SSMS and re-entered and the Invalid Object Name error no longer appeared. I presume that refreshed the Intellisense as well.

0
votes

In my case on SQL Server 2014 Express, the stored procedure name value showing this error message

Invalid object name "SP_NAME"

even if the stored procedure has already been created and executing successfully. But after enable IntelliSense, error was removed and every stored procedure query working fine.

enter image description here