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 SQL Server Keeps giving this error      Invalid column name  INCL GSTAMOUNT      Can anyone please tell me what s wrong here

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

with refresh table or close and open sql server this work

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] SQL Server: Invalid Column Name

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to stored-procedures