“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

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]

Please show us your table definition and your query where you get an error — glautrou, Aug 13 '13 at 14:55
I am sorry if am writing false things .. but can you please post your actual query .. it will be more helpfull — Dhaval, Aug 13 '13 at 15:03
@jackncoke do you add the column and attempt to use it right away after adding? — Yuriy Galanter, Aug 13 '13 at 15:04

Christian Phillips · Answer 1 · 2013-08-13T15:39:02.713

2

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

edited Aug 13 '13 at 15:39

answered Aug 13 '13 at 15:04

Christian Phillips

18,399
8
53
82

2

You're missing an open bracket on the second line (the insert statement). – Bridge Aug 13 '13 at 15:05
There was a error in my syntax after i copied it to shorten it. – jackncoke Aug 13 '13 at 15:24

Dave Simione · Accepted Answer · 2013-08-13T15:30:17.177

2

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

Is your SP switching to a different database that possibly has the same table structure (but wasn't updated)?
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.

edited Aug 13 '13 at 15:30

answered Aug 13 '13 at 15:09

Dave Simione

1,441
2
21
31

I agree - it's unlikely to be something more sinister. Think simple first! – Bridge Aug 13 '13 at 15:12
Thank you! I had a miss reference to a production table that should of been a development table. – jackncoke Aug 13 '13 at 15:30
2

@jackncoke, you can use colour coding for the environments, check out this link http://evanon.net/blog/post/2011/04/16/Colour-coded-Environments-in-SQL-Server-Management-Studio-2008.aspx – Christian Phillips Aug 13 '13 at 15:38

score 0 · Answer 3 · edited Aug 13 '13 at 15:08

0

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];

edited Aug 13 '13 at 15:08

Bridge

29,818
9
60
82

answered Aug 13 '13 at 15:05

CRAFTY DBA

14,351
4
26
30

There was a error in my syntax after i copied it to shorten it. – jackncoke Aug 13 '13 at 15:24

“Invalid column name” sql server 2005

3 Answers3