SQL Server Management Studio 2014 12.0.5000.0 "Invalid Column Name" Error

Question

The following code executes as expected. However, the editor shows red lines under attribute5 and WCC_ID in "h.attribute5 = i.WCC_ID" and states "Invalid column name" when the mouse hovers over the red lines. These columns exist in the respective tables.

What is wrong with the SQL?

SELECT 
*
FROM
(
SELECT attribute5
FROM
OPENQUERY([FAMISPRO.WORLD],'SELECT attribute5 FROM FAMIS_EMP') 
) AS h
INNER JOIN
(
SELECT DISTINCT h.WCC_ID, h.KEYMARK
FROM
OPENQUERY ( PROJECT ,'SELECT * FROM ENTRY;' ) AS h
LEFT JOIN
(
SELECT *
FROM
OPENQUERY ( PROJECT ,'SELECT * FROM ENTRY;' )  
WHERE
EVENT = 'RETURNED'
) AS i
ON
h.WCC_ID = i.WCC_ID AND h.KEYMARK = i.KEYMARK
WHERE
i.EVENT IS NULL
) AS i
ON
h.attribute5 = i.WCC_ID;

Nothing is wrong with the SQL. The red squiggly lines are just IntelliSense warnings because IntelliSense can't correctly parse the metadata. It could be because it doesn't know what is coming out of dynamic SQL or it could just need to be refreshed (Ctrl+Shift+R). You can ignore these underlines or, if they bother you that much, just turn IntelliSense off. — Aaron Bertrand, Oct 07 '16 at 15:43
@Aaron Bertrand; Thank you. I discovered that removing the SELECT statement that wraps the first OPENQUERY removes the error. — user107591, Oct 07 '16 at 16:06

score 1 · Answer 1 · edited Oct 09 '17 at 12:12

If your database [FAMISPRO.WORLD] is a linked server then you'll always get red line. SQL Server doesn't hold meta data about linked servers so there is no way to verify if a column exists in a table or not and this results in a red line under it.

When you navigate to tables in linked server database, you won't see little + sign in front of it as you see in your normal database tables because management studio only allowed to access tables and views from linked server database.

However if you want to verify column exist or not use below query.

Select * from [FAMISPRO].[WORLD].INFORMATION_SCHEMA.COLUMNS 
where table_name like 'FAMIS_EMP'

You can also use some sys tables too in linked server.

SQL Server Management Studio 2014 12.0.5000.0 "Invalid Column Name" Error

1 Answers1