SQL Server 报告“无效的列名”，但该列存在并且查询通过管理工作室工作
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7260487/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server reports 'Invalid column name', but the column is present and the query works through management studio
提问by kidl33t
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 Studiowhen run against the same database.
我陷入了一些僵局。我有一个由某些C#代码生成的查询。Microsoft SQL Server Management Studio当针对同一数据库运行时，查询工作正常。
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.
有问题的列最近被添加到数据库中。它是一个名为 的日期列Incident_Begin_Time_ts。
An example that fails is:
一个失败的例子是：
select * from PerfDiag
where Incident_Begin_Time_ts > '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.
Select MAX(Incident_Being_Time_ts);在代码中运行时，其他查询也会失败，因为它认为该列丢失。
Any ideas?
有任何想法吗？
采纳答案by Nicholas Carey
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).
我怀疑你有两个同名的表。一个由架构 'dbo' ( dbo.PerfDiag) 拥有，另一个由用于连接到 SQL Server 的帐户的默认架构（类似于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'.
- 在用户的默认架构下。
- 在架构“dbo”下。
The unqualified reference is bound to the first match in the above sequence.
非限定引用绑定到上述序列中的第一个匹配项。
As a general recommended practice, one should alwaysqualify 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.
不合格的引用可能会使存储过程或查询的缓存执行计划无效，因为引用绑定到的架构可能会根据执行存储过程或查询的凭据而改变。这会导致重新编译查询/存储过程，从而影响性能。重新编译会导致编译锁被取消，从而阻止其他人访问所需的资源。
名称解析会减慢查询执行速度，因为必须进行两个探测才能解析为对象的可能版本（“dbo”拥有的版本）。这是通常的情况。单个探测器将解析名称的唯一时间是当前用户是否拥有指定名称和类型的对象。
[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.
- 您没有连接到您认为的数据库。
- 您没有连接到您认为的 SQL Server 实例。
Double check your connect strings and ensure that they explicitly specify the SQL Server instance name and the database name.
仔细检查您的连接字符串并确保它们明确指定了 SQL Server 实例名称和数据库名称。
回答by Mangesh
Just press Ctrl+ Shift+ Rand see...
只需按Ctrl+ Shift+R并查看...
In SQL Server Management Studio, Ctrl+Shift+R refreshes the local cache.
在 SQL Server Management Studio 中，Ctrl+Shift+R 刷新本地缓存。
回答by Stagg
If you are running this inside a transaction and a SQL statement before this drops/alters the table you can also get this message.
如果您在删除/更改表之前在事务和 SQL 语句中运行它，您也可以获得此消息。
回答by IAM_AL_X
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.
我最终关闭并重新启动了 Microsoft SQL Server Management Studio；这为我修复了它。但在其他时候，只需启动一个新的查询窗口就足够了。
回答by Dévan Coetzee
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.
如果您使用与列同名的变量，则可能是您忘记了“@”变量标记。在 INSERT 语句中，它将被检测为一列。
回答by ApplePie
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.
刚刚遇到了完全相同的问题。我在临时表中重命名了一些别名列，该表由同一代码的另一部分进一步使用。出于某种原因，这并没有被 SQL Server Management Studio 捕获并且它抱怨无效的列名。
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 代码复制粘贴到这个新查询中，然后再次运行它。这似乎正确地刷新了环境。
回答by Rexhi
In my case I restart Microsoft SQL Sever Management Studio and this works well for me.
就我而言，我重新启动了 Microsoft SQL Sever Management Studio，这对我来说效果很好。
回答by cdabel
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:
使用表值运行标量函数时出现此错误，但标量函数 RETURN 子句中的 Select 语句缺少“FROM 表”部分。：脸掌：
回答by Иво Недев
Also happens when you forget to change the ConnectionString and ask a table that has no idea about the changes you're making locally.
当您忘记更改 ConnectionString 并询问一个不知道您在本地进行的更改的表时，也会发生这种情况。
回答by Deepak Kataria
In my case I was trying to get the value from wrong ResultSet when querying multiple SQL statements.
在我的情况下，我试图在查询多个 SQL 语句时从错误的 ResultSet 中获取值。
