“Invalid column name” sql server 2005

Question

I have two tables that i added a new column to. The column name is LocationKey. Whenever i try and use that column in a alter stored procedure i get a error message below. I went back and copy pasted the column name. It is exact! it is where it needs to be on both tables.

 Msg 207, Level 16, State 1, Procedure usp_MY_Stored_procedure, Line 122
Invalid column name 'LocationKey'.

I have googled and nothing that i came up with has resolved the problem. What could i be missing?

Link i have read: 1. "Invalid column name" error when calling insert after table created

2. http://social.msdn.microsoft.com/Forums/sqlserver/en-US/87ef4b02-6a57-4cae-9e0c-bf705f3c2bec/invalid-column-name-after-adding-new-column

3. http://www.mssqltips.com/sqlservertip/2591/troubleshooting-intellisense-in-sql-server-management-studio-2012/

Anything i can try to troubleshoot this further would be greatly appreciated.

-- DROP LEADING DOUBLE QUOTE (and PhotoUrl column)
    INSERT INTO [table2](

        [Comment1]
        ,[Comment 2]
        ,[Comment 3]
        ,[Comment 4]
        ,[Name]
        ,[PhotoUrl]
        ,[LocationKey]

        )



    SELECT 
         [Comment1]
        , [Comment 2]
        , [Comment 3]
        , [Comment 4]
        , SUBSTRING([Name],1,100)-- Limit to 100 chars
        , [PhotoUrl]  -- varchar(MAX)
        , [LocationKey]


    FROM [table1]

Answer 1

You need to change the SQL, since the syntax looks incorrect, try this...

INSERT INTO [table2](

        [Comment1]
        ,[Comment 2]
        ,[Comment 3]
        ,[Comment 4]
        ,[Name]
        ,[PhotoUrl]
        ,[LocationKey]

        )



    SELECT 
         [Comment1]
        , [Comment 2]
        , [Comment 3]
        , [Comment 4]
        , SUBSTRING([Name],1,100)-- Limit to 100 chars
        , [PhotoUrl]  -- varchar(MAX)
        , [LocationKey]


    FROM [table1]

EDIT: Based on comments

If you want to colour code the different environments, you can use this tip http://evanon.net/blog/post/2011/04/16/Colour-coded-Environments-in-SQL-Server-Management-Studio-2008.aspx

Answer 2

In case the comma issue in the other answers is just a typo in your question, two other thoughts come to mind:

1. Is your SP switching to a different database that possibly has the same table structure (but wasn't updated)?
2. Do you have multiple copies of the database (development, production, etc), and you added the column to a different server than where you are trying to change the SP?

Also, you can copy and paste your SELECT statement to a new query, and try running it. That will tell you if the invalid column error is coming from Table1 or Table2.

Another thought - are you possibly querying through a view, and not direct to the table? If the view isn't defined with SCHEMABINDING, you'll need to refresh the view to have the new field appear.

Answer 3

Your syntax is wrong. Remove leading commas, add parenthesis. See updated code below.

INSERT INTO [table2]
(
  [Comment1]
 ,[Comment 2]
 ,[Comment 3]
 ,[Comment 4]
 ,[Name]
 ,[PhotoUrl]
 ,[LocationKey]
)

SELECT
  [Comment1]
 ,[Comment 2]
 ,[Comment 3]
 ,[Comment 4]
 ,SUBSTRING([Name],1,100)
 ,[PhotoUrl]
 ,[LocationKey]
FROM [table1];