Invalid Column Name SQL Error

SQL Server: Invalid Column Name

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.

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

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) — one not qualified by schema name — 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.

Getting sql error invalid column name while using Having

A few ways you can approach this. Hopefully the following is syntactically correct as obviously unable to directly test.

You can use a derived query or CTE such as

with d as (
 LocationId, 
 (
   3959 *
   acos(cos(radians(37)) * 
   cos(radians(Latitude)) * 
   cos(radians(Longitude) - 
   radians(-122)) + 
   sin(radians(37)) * 
   sin(radians(Latitude)))
 ) AS distance 
 FROM dbo.UserLocation
)
select *
from d
where distance < 28
order by distance

You could also use an apply

select LocationId, distance
from dbo.UserLocation
cross apply(values(
   3959 *
   Acos(Cos(Radians(37)) * 
   Cos(Radians(Latitude)) * 
   Cos(Radians(Longitude) - 
   Radians(-122)) + 
   Sin(Radians(37)) * 
   Sin(Radians(Latitude)))
    ))v(distance)
where distance < 28
order by distance

SQL , getting invalid column name error for a piece of code working perfectly online

Wrap your case expression query up in a derived table. GROUP BY its result:

select Region, age_group, count(*) freq
from
(
    select Region,   
           case when Age>54 then 'old'
                when Age<36 then 'young'
                else 'mid' end as age_group
    from dbo.tblCustomer 
) dt
group by Region, age_group
order by Region, freq desc;

Invalid column name when using dynamic SQL

You need to parameterize you dynamic query. So you pass @TableName all the way through

CREATE PROCEDURE MergeTable @TableName NVARCHAR(max) 
AS

DECLARE @MergeStatement NVARCHAR(max);

SET @MergeStatement = '
SELECT Query
FROM dbo.QueryMergeDWH
WHERE SourceTableName = @TableName;
';

EXEC sp_executesql
    @MergeStatement,
    N'@TableName nvarchar(max)',
    @TableName = @TableName;

GO

But it's unclear what's dynamic about that, you could just as well do

CREATE PROCEDURE MergeTable @TableName NVARCHAR(max) 
AS

SELECT Query
FROM dbo.QueryMergeDWH
WHERE SourceTableName = @TableName;

GO

---

Related Topics