SQL Server 报告“无效的列名"，但该列存在并且查询通过管理工作室工作 [英] SQL Server reports 'Invalid column name', but the column is present and the query works through management studio

问题描述

我陷入了僵局.我有一个由一些 C# 代码生成的查询.当针对同一数据库运行时，该查询在 Microsoft SQL Server Management Studio 中运行良好.

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.

有问题的列最近被添加到数据库中.这是一个名为 Incident_Begin_Time_ts 的日期列.

The column in question was recently added to the database. It is a date column called Incident_Begin_Time_ts .

失败的例子是:

select * from PerfDiag 
where Incident_Begin_Time_ts > '2010-01-01 00:00:00';

其他查询，如 Select MAX(Incident_Being_Time_ts); 在代码中运行时也会失败，因为它认为该列丢失.

Other queries like Select MAX(Incident_Being_Time_ts); also fail when run in code because it thinks the column is missing.

有什么想法吗?

推荐答案

我怀疑您有两个同名的表.一个由架构dbo"(dbo.PerfDiag)所有，另一个由用于连接到 SQL Server 的帐户的默认架构(类似于 userid.PerfDiag).

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:

• 在用户的默认架构下.
• 在架构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).

名称解析会减慢查询执行速度，因为必须进行两个探测才能解析到对象的可能版本(由dbo"拥有).这是通常的情况.单个探测器将解析名称的唯一时间是当前用户是否拥有指定名称和类型的对象.

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.

其他可能性是(排名不分先后):

The other possibilities are (in no particular order):

• 您没有连接到您认为的数据库.
• 您没有连接到您认为的 SQL Server 实例.

仔细检查您的连接字符串并确保它们明确指定了 SQL Server 实例名称和数据库名称.

Double check your connect strings and ensure that they explicitly specify the SQL Server instance name and the database name.

这篇关于SQL Server 报告“无效的列名"，但该列存在并且查询通过管理工作室工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！