Seeing invalid column and object everytime I create StoreProcedure or insert a new column

Question

Seeing invalid column and object everytime I create StoreProcedure or insert a new column

karenworld

SSChasing Mays

Points: 654
More actions
February 20, 2017 at 7:32 pm

#327546

Hi experts,
I am really new in this IT field and in my project I faced this problem in experiencing frequent invalid object and column name, even thought there is no syntax error whatsoever in my sql statement.
After a few hours, the invalid objects will be gone.
However, it's really disturbing for me, as I am constantly wondering if the Stored Procedure created can be used.
I was told to either refresh the table or create an insert statement everytime to test things out.
Is this the way how SQL 2008 behave ? I mean it is really to use something that is so unstable.
Please see attached picture -- one that has invalid object and a similar one but with the invalid object gone after a few hours.
Really hope to have honest opinion on this.
The system is not local. I am given a username and password to access a server database.
Tks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

happycat59 One Orange Chip Points: 29386 More actions · Answer 1

I think that the problem is related to how SSMS caches meta data (i.e. information about what tables, columns etc exist in the database). It does not keeps its cache up to date with the actually meta data in the database. Instead it does it when you connect to a database for the first time in a session or when you explicitly tell SSMS to refresh its cache (in the Edit menu, you should see an option called "IntelliSense". In the Intellisense menu, click on "Refresh Local Cache")

Refreshing the local cache that SSMS uses should fix your issue.

karenworld SSChasing Mays Points: 654 More actions · Answer 2

Hi, I followed the advice of this blog below:
http://blogs.visoftinc.com/2014/02/21/ssms-and-the-intellisense-red-squiggle-mess/

But, it did not help.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Click the Edit menu in Management Studio. Select Intellisense (at the bottom) and then Refresh Local Cache. (Ctrl-Shift-R as a shortcut)

It's purely the delay in SSMS refreshing the schema. Nothing to do with the SQL DB engine itself.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karenworld SSChasing Mays Points: 654 More actions · Answer 4

Well, all these red invalid objects and columns are so disturbing to me.

Furthermore, my project Supervisor said that cannot have problem, cos Nobody has this problem except me and everyone is accessing the same database server.
What do you think since I am the only one getting it ?

sgmunson SSC Guru Points: 110639 More actions · Answer 5

karenworld - Tuesday, February 21, 2017 2:48 AM
Well, all these red invalid objects and columns are so disturbing to me.
Furthermore, my project Supervisor said that cannot have problem, cos Nobody has this problem except me and everyone is accessing the same database server.
What do you think since I am the only one getting it ?

One possibility is that your "current database", according to SSMS, may not be the database that you intend to work with. If you don't have that set correctly, the metadata cache will not have the correct information in it, and even a CTRL-SHIFT-R isn't going to fix it. There's a drop-down in the upper left area of the SSMS window that indicates what SSMS thinks is the database you're working with. Assuming that you need to properly set that value, you'll then want to do the cache refresh (ctrl-shift-r) and the invalid object indicators should then disappear.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

SQLBill SSC Guru Points: 51440 More actions · Answer 6

Are you positive your query window is connected to the correct server/database?

I've had issues where I have connected to two or more different servers and when I open a query window, I find that it (the query window) isn't connected to the server I thought I was connecting to. The bottom of the query window will show which server you are connected to. Then double-check which database you are connected to. If you have multiple copies of the same database on one server, you might be connected to the wrong one.

-SQLBill

karenworld SSChasing Mays Points: 654 More actions · Answer 7

sgmunson - Wednesday, February 22, 2017 1:52 PM
karenworld - Tuesday, February 21, 2017 2:48 AM
Well, all these red invalid objects and columns are so disturbing to me.
Furthermore, my project Supervisor said that cannot have problem, cos Nobody has this problem except me and everyone is accessing the same database server.
What do you think since I am the only one getting it ?
One possibility is that your "current database", according to SSMS, may not be the database that you intend to work with. If you don't have that set correctly, the metadata cache will not have the correct information in it, and even a CTRL-SHIFT-R isn't going to fix it. There's a drop-down in the upper left area of the SSMS window that indicates what SSMS thinks is the database you're working with. Assuming that you need to properly set that value, you'll then want to do the cache refresh (ctrl-shift-r) and the invalid object indicators should then disappear.

Well, I was given the username and password to assess. So, if I could create tables inside, then I was assessing the right database isn't it? And the invalid object will disappear after a few hours anyway.
So, it goes to show that the tables really existed there in the first place.
What I don't understand is I was told nobody got this problem except me.

sgmunson SSC Guru Points: 110639 More actions · Answer 8

karenworld - Wednesday, February 22, 2017 6:31 PM
sgmunson - Wednesday, February 22, 2017 1:52 PM
karenworld - Tuesday, February 21, 2017 2:48 AM
Well, all these red invalid objects and columns are so disturbing to me.
Furthermore, my project Supervisor said that cannot have problem, cos Nobody has this problem except me and everyone is accessing the same database server.
What do you think since I am the only one getting it ?
One possibility is that your "current database", according to SSMS, may not be the database that you intend to work with. If you don't have that set correctly, the metadata cache will not have the correct information in it, and even a CTRL-SHIFT-R isn't going to fix it. There's a drop-down in the upper left area of the SSMS window that indicates what SSMS thinks is the database you're working with. Assuming that you need to properly set that value, you'll then want to do the cache refresh (ctrl-shift-r) and the invalid object indicators should then disappear.
Well, I was given the username and password to assess. So, if I could create tables inside, then I was assessing the right database isn't it? And the invalid object will disappear after a few hours anyway.
So, it goes to show that the tables really existed there in the first place.
What I don't understand is I was told nobody got this problem except me.

Just having a username and password does NOT necessarily mean that when you log in to that server via SSMS that your default database is automatically going to be the one you want to work with. You have to actually check which one comes up in the drop down. If it's not the right one, change it to the right one and then do the CTRL-SHIFT-R key combination, and SSMS will refresh it's cache with that from the selected database.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)