SQL Server: Invalid Column Name

Question

I am working on modifying the existing SQL Server stored procedure. I added two new columns to the table and modified the stored procedure as well to select these two columns as well. Although the columns are available in the table, I keep getting this error:

Invalid column name 'INCL_GSTAMOUNT'

enter image description here

Can anyone please tell me what's wrong here?

have you executed it - do you get that error? intellisense can take some time to update? — , Sep 22 '13 at 07:44

score 319 · Accepted Answer · edited Sep 17 '15 at 10:49

319

Whenever this happens to me, I press Ctrl+Shift+R which refreshes intellisense, close the query window (save if necessary), then start a new session which usually works quite well.

edited Sep 17 '15 at 10:49

Salah Akbari

39,330
10
79
109

answered Sep 22 '13 at 08:05

Ric

12,855
3
30
36

17

I have found the keystroke sufficient (great thanks!) - no need to close the query window – sparrow Jun 06 '16 at 14:03

score 135 · Answer 2 · answered Mar 07 '14 at 03:30

135

Could also happen if putting string in double quotes instead of single.

answered Mar 07 '14 at 03:30

Harry

1,765
1
12
12

2

That's was the reason in my case - can you please explain the reason for this – Mani Sep 01 '15 at 07:35
3

Ugh... THANK YOU! So dumb. Had this problem with querying for date range with dates in double-quotes. – conner.xyz Jul 19 '16 at 22:29
This solution also applies to sybase ASE – Chexpir Jun 11 '20 at 08:52
6

When using double quotes, it's parsed as a column name instead of a string. If that column name is not available (a string usually does not exist as a column), you'll get this error. – Jim Lutz Aug 31 '20 at 13:52
You can use double quotes around column names to include chars that are not normally allowed or are normally reserved key words. More info: https://learn.microsoft.com/en-us/sql/t-sql/statements/set-quoted-identifier-transact-sql?view=sql-server-ver15 – Jon.Mozley Apr 13 '21 at 14:27
Thank you so very much for this nugget of information, I've been on this for two very frustrating hours. – monn3t Jul 09 '22 at 20:47
Oh dear, after 30min debugging and googling it was just the quotes usage. Thank you for the tip! – BrTkCa Dec 09 '22 at 13:35

score 8 · Answer 3 · answered Oct 29 '20 at 06:50

If you are going to ALTER Table column and immediate UPDATE the table including the new column in the same script. Make sure that use GO command to after line of code of alter table as below.

ALTER TABLE Location 
ADD TransitionType SMALLINT NULL
GO   

UPDATE Location SET TransitionType =  4

ALTER TABLE Location 
    ALTER COLUMN TransitionType SMALLINT NOT NULL

score 7 · Answer 4 · answered Oct 11 '21 at 19:13

7

I came here because I was getting this error. And the reason was that I was using double quotes (") instead of single quotes (') when giving values for WHERE conditions. Writing this for the future me.

answered Oct 11 '21 at 19:13

gthuo

2,376
5
23
30

score 5 · Answer 5 · answered Mar 09 '16 at 09:22

This error may ALSO occur in encapsulated SQL statements e.g.

DECLARE @tableName nvarchar(20) SET @tableName = 'GROC'

DECLARE @updtStmt nvarchar(4000)

SET @updtStmt = 'Update tbProductMaster_' +@tableName +' SET department_str = ' + @tableName exec sp_executesql @updtStmt

Only to discover that there are missing quotations to encapsulate the parameter "@tableName" further like the following:

SET @updtStmt = 'Update tbProductMaster_' +@tableName +' SET department_str = ''' + @tableName + ''' '

Thanks

score 2 · Answer 6 · answered Sep 22 '13 at 08:03

2

Intellisense is not auto refreshed and you should not fully rely on that

answered Sep 22 '13 at 08:03

Madhivanan

13,470
1
24
29

score 2 · Answer 7 · answered Feb 07 '17 at 07:09

2

I just tried. If you execute the statement to generate your local table, the tool will accept that this column name exists. Just mark the table generation statement in your editor window and click execute.

answered Feb 07 '17 at 07:09

Anderas

31
1

This solution was the only one to work for me. I tried refreshing Intellisense Local Cache, Recreating Stored Proc, Reconnect to SQL Server, nothing worked until I just ran code to create table with new column name, then change to insert worked. – Michael Bruesch Dec 12 '19 at 16:09

score 1 · Answer 8 · answered Apr 14 '16 at 03:46

I was getting the same error when creating a view.

Imagine a select query that executes without issue:

select id
from products

Attempting to create a view from the same query would produce an error:

create view app.foobar as
select id
from products

Msg 207, Level 16, State 1, Procedure foobar, Line 2
Invalid column name 'id'.

For me it turned out to be a scoping issue; note the view is being created in a different schema. Specifying the schema of the products table solved the issue. Ie.. using dbo.products instead of just products.

score 1 · Answer 9 · answered Jan 23 '18 at 09:50

1

Following procedure helped me solve this issue but i don't know why.

Cut the code in question given by the lines in the message
Save the query (e.g. to file)
Paste the code to where it was before
Again save the query

Even if it seems to be the same query executing it did not throw this error

answered Jan 23 '18 at 09:50

Dreanaught

71
1
10

I had a similar problem. I think an editors window save temporary data (exp. temporary table). Maybe these steps help somebody 1. reset editor window 2. reset data (DROP TABLE #TempTable) 3. reset SQL Server – SVPopov May 06 '20 at 16:52

score 1 · Answer 10 · answered Dec 19 '18 at 11:12

There can be many things:
First attempt, make a select of this field in its source table;
Check the instance of the sql script window, you may be in a different instance;
Check if your join is correct;
Verify query ambiguity, maybe you are making a wrong table reference
Of these checks, run the T-sql script again

[Image of the script SQL][1]
  [1]: https://i.stack.imgur.com/r59ZY.png`enter code here

score 1 · Answer 11 · answered Feb 08 '22 at 08:44

1

I experienced similar problem when running a query from the code (C#) side. The column in the table that was bringing the above error had 'default value or binding' (when I checked the table's design) already added. So I just removed the column and its corresponding value as data being inserted by the query

answered Feb 08 '22 at 08:44

Timothy Odhiambo

19
6

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Feb 08 '22 at 08:46

score 1 · Answer 12 · answered May 23 '22 at 14:19

1

I am working with Blazor and forgot to use any quotations...

SqlDataAdapter da = new($"select * from table where column = {value}", con);

needed to be

SqlDataAdapter da = new($"select * from table where column = '{value}'", con);

Thanks to Harry's answer above for sending down the right train of thought.

answered May 23 '22 at 14:19

L__

51
3

Please don't add "thank you" as an answer. Instead, vote up the answers that you find helpful. - [From Review](/review/late-answers/31844013) – Hoppeduppeanut May 27 '22 at 04:59
1

I upvoted when I wrote my original response. I would have replied to Harry's answer directly, but I do not have a rating of 50 to do so. – L__ May 27 '22 at 12:58

score 0 · Answer 13 · answered Sep 22 '13 at 09:41

0

with refresh table or close and open sql server this work

answered Sep 22 '13 at 09:41

behzad

194
6
21

score 0 · Answer 14 · answered Jun 18 '19 at 08:31

0

Refresh your tables.
Restart the SQL server.
Look out for the spelling mistakes in Query.

answered Jun 18 '19 at 08:31

Khizer Amin

1
1

score 0 · Answer 15 · answered Oct 25 '19 at 20:19

I noted that, when executing joins, MSSQL will throw "Invalid Column Name" if the table you are joining on is not next to the table you are joining to. I tried specifying table1.row1 and table3.row3, but was still getting the error; it did not go away until I reordered the tables in the query. Apparently, the order of the tables in the statement matters.

+-------------+    +-------------+    +-------------+    
| table1      |    | table2      |    | table3      |    
+-------------+    +-------------+    +-------------+    
| row1 | col1 |    | row2 | col2 |    | row3 | col3 |    
+------+------+    +------+------+    +------+------+    
| ...  | ...  |    | ...  | ...  |    | ...  | ...  |    
+------+------+    +------+------+    +------+------+

SELECT * FROM table1, table2 LEFT JOIN table3 ON row1 = row3; --throws an error

SELECT * FROM table2, table1 LEFT JOIN table3 ON row1 = row3; --works as expected

score 0 · Answer 16 · answered Apr 07 '21 at 14:03

Not enough rep to comment, so I'll write a new answer, re: Nick Reed's answer regarding the ordering of the tables in the query.

The JOIN operation has two operands. In the ON clause, one may only refer to columns from those operands. In Nick's first example,

SELECT * FROM table1, table2 LEFT JOIN table3 ON row1 = row3; --throws an error

table2 and table3 are the operands of the JOIN, but the ON clause refers to row1, defined in table1. Hence the error.

score 0 · Answer 17 · answered Mar 09 '22 at 21:20

0

When this happened to me, the good old quit and re-launch SQL server management studio did the trick for me.

answered Mar 09 '22 at 21:20

Bug Hunter Zoro

1,743
18
21

score 0 · Answer 18 · answered Mar 15 '22 at 02:16

0

I was using DbUp to add a column to a table then in the same script an UPDATE on that column and it said "invalid column <column name>".

Breaking the two statements into separate scripts resolved the issue.

answered Mar 15 '22 at 02:16

chmoder

876
10
19

SQL Server: Invalid Column Name

18 Answers18

Linked