SQL Server reports Invalid column name but the column is present and the query works through management studio

Question

I ve hit a bit of an impasse  I have a query that is generated by some C  code  The query works fine in Microsoft SQL Server Management Studio when run against the same database   However when my code tries to run the same query I get the same error about an invalid column and an exception is thrown  All queries that reference this column are failing   The column in question was recently added to the database  It is a date column called Incident Begin Time ts      An example that fails is   select   from PerfDiag  where Incident Begin Time ts  gt   2010-01-01 00 00 00     Other queries like Select MAX Incident Being Time ts   also fail when run in code because it thinks the column is missing     Any ideas

User · Answer

If you are using variables with the same name as your column  it could be that you forgot the     variable marker  In an INSERT statement it will be detected as a column

User · Answer

In my case it seems the problem was a weird caching problem  The solutions above didn t work   If your code was working fine and you added a column to one of your tables and it gives the  invalid column name  error  and the solutions above doesn t work  try this  First run only the section of code for creating that modified table and then run the whole code

User · Answer

Just press Ctrl   Shift   R and see     In SQL Server Management Studio  Ctrl Shift R refreshes the local cache

User · Answer

Also happens when you forget to change the ConnectionString and ask a table that has no idea about the changes you re making locally

User · Answer

I eventually shut-down and restarted Microsoft SQL Server Management Studio  and that fixed it for me   But at other times  just starting a new query window was enough

User · Answer

Including this answer because this was the top result for  invalid column name sql  on google and I didn t see this answer here  In my case  I was getting Invalid Column Name  Id1 because I had used the wrong id in my  HasForeignKey statement in my Entity Framework C  code  Once I changed it to match the  HasOne   object s id  the error was gone

User · Answer

If you are running this inside a transaction and a SQL statement before this drops alters the table you can also get this message

User · Answer

I suspect that you have two tables with the same name  One is owned by the schema  dbo   dbo PerfDiag   and the other is owned by the default schema of the account used to connect to SQL Server  something like userid PerfDiag    When you have an unqualified reference to a schema object  such as a table   mdash  one not qualified by schema name  mdash  the object reference must be resolved  Name resolution occurs by searching in the following sequence for an object of the appropriate type  table  with the specified name  The name resolves to the first match    Under the default schema of the user  Under the schema  dbo     The unqualified reference is bound to the first match in the above sequence   As a general recommended practice  one should always qualify references to schema objects  for performance reasons    An unqualified reference may invalidate a cached execution plan for the stored procedure or query  since the schema to which the reference was bound may change depending on the credentials executing the stored procedure or query  This results in recompilation of the query stored procedure  a performance hit  Recompilations cause compile locks to be taken out  blocking others from accessing the needed resource s   Name resolution slows down query execution as two probes must be made to resolve to the likely version of the object  that owned by  dbo    This is the usual case  The only time a single probe will resolve the name is if the current user owns an object of the specified name and type     Edited to further note   The other possibilities are  in no particular order     You aren t connected to the database you think you are  You aren t connected to the SQL Server instance you think you are    Double check your connect strings and ensure that they explicitly specify the SQL Server instance name and the database name

User · Answer

I ve gotten this error when running a scalar function using a table value  but the Select statement in my scalar function RETURN clause was missing the  FROM table  portion   facepalms

User · Answer

In my case I restart Microsoft SQL Sever Management Studio and this works well for me

User · Answer

In my case I was trying to get the value from wrong ResultSet when querying multiple SQL statements

User · Answer

Just had the exact same problem  I renamed some aliased columns in a temporary table which is further used by another part of the same code  For some reason  this was not captured by SQL Server Management Studio and it complained about invalid column names   What I simply did is create a new query  copy paste the SQL code from the old query to this new query and run it again  This seemed to refresh the environment correctly

[sql] SQL Server reports 'Invalid column name', but the column is present and the query works through management studio

Examples related to sql

Examples related to sql-server