SQL Server: Invalid Column Name
SqlSql ServerSql Server-2008Stored ProceduresSql Problem Overview
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'
Can anyone please tell me what's wrong here?
Sql Solutions
Solution 1 - Sql
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.
Solution 2 - Sql
Could also happen if putting string in double quotes instead of single.
Solution 3 - Sql
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
Solution 4 - Sql
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
Solution 5 - Sql
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.
Solution 6 - Sql
Intellisense is not auto refreshed and you should not fully rely on that
Solution 7 - Sql
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.
Solution 8 - Sql
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.
Solution 9 - Sql
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
Solution 10 - Sql
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
Solution 11 - Sql
with refresh table or close and open sql server this work
Solution 12 - Sql
I had a similar problem.
Issue was there was a trigger on the table that would write changes to an audit log table. Columns were missing in audit log table.
Solution 13 - Sql
- Refresh your tables.
- Restart the SQL server.
- Look out for the spelling mistakes in Query.
Solution 14 - Sql
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
Solution 15 - Sql
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.
Solution 16 - Sql
If you store integer values in a varchar column, that error happens.
Insert from php use $item1 to '$item1' make variable int to string
Solution 17 - Sql
Resetting the IntelliSense would do the job.
Magic Command: CTRL + SHIFT + R
Enjoy!!!
Solution 18 - Sql
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
Solution 19 - Sql
When this happened to me, the good old quit and re-launch SQL server management studio did the trick for me.
Solution 20 - Sql
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.
Solution 21 - Sql
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.