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

  • karenworld

    Mr or Mrs. 500

    Points: 554

    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.

  • happycat59

    One Orange Chip

    Points: 29186

    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

    Mr or Mrs. 500

    Points: 554

    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: 1004446

    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

    Mr or Mrs. 500

    Points: 554

    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: 110437

    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)
    ‌:) 🙂 🙂
    Health & Nutrition

  • SQLBill

    SSC Guru

    Points: 51440

    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

    Mr or Mrs. 500

    Points: 554

    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: 110437

    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)
    ‌:) 🙂 🙂
    Health & Nutrition

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

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