SQL Server Invalid Column name after adding new column

73
votes

I just added an identity column to an existing table with data through the SSMS Designer, the table updates fine and I can run a select query against it successfully, but after the query finishes I noticed that the new column is underlined in red with an error indicating it's an invalid column name.

Why does this occur? I would expect the query to fail if the column was invalid.

Does the SSMS keep some type of internal reference to the old table that must be flushed?

I tried closing the DB connection and reopening it, but still the same error. Not sure what I'm missing here.

SELECT TOP 100 
[PRIMARY_NDX_Col1],
[NEW_Col], --QUERY EXECUTES SUCCESSFULLY, BUT THIS IS UNDERLINED RED AS AN INVALID COLUMN
[Col3]
FROM [dbo].[MyTable]
1
sqlsql-serversql-server-2008tsqlsql-server-2005
Just tried restarting and that fixed it. Wonder if there's a way to dynamically clear the SSMS 2008 cache after editing a table. This seems like the kind of bug people could spend hours trying to troubleshoot. – kingrichard2005
You don't need to restart SSMS to fix this. Keep this article handy, one of the most common causes is that SSMS just isn't fast enough in refreshing its local cache of metadata. mssqltips.com/sqlservertip/2591/… – Aaron Bertrand
Please explain why the answers provided are not acceptable for this question so that enhancements to them may be provided. @AaronBertrand good tips in there! – Mark Schultheiss

1 Answers

207
votes

you can either

  • Press Ctrl+Shift+R

OR

  • Go to Edit >> IntelliSense >> Refresh Local Cache