重复的列名...但仅当数据库不区分大小写时
我正在尝试从链接服务器连接查询具有区分大小写排序规则的数据库视图。其他需要注意的是我试图查询的视图基本上是三个索引视图的包装。
我收到的错误是：
通过 OPENQUERY 和 OPENROWSET 获取的结果集中不允许有重复的列名。列名“ExtPrice”重复。
在视图中，我有 EXTPRICE 以及 ExtPrice。当我直接从服务器查询此视图时，没有任何问题...但是当我尝试通过链接服务器使用查询时，出现上述错误。
- 这是排序问题吗？
- 如何通过链接服务器使用此视图，并且仍然让查询了解该视图区分大小写？
如果这是一个愚蠢的问题，我提前道歉。
I'm trying to query a view on a database that has case sensitive collation from a linked server connection. Something else to note is the view I'm trying to query is basically a wrapper to three indexed views.
The error i'm getting is:
Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET. The column name "ExtPrice" is a duplicate.
In the view I have EXTPRICE as well as ExtPrice. When I query this view from the server directly, I don't have any issues... but when I try to use the query via a linked server, I get the above error.
- Is this a collation issue?
- How do I use this view via a linked server and still have the query understand that the view is case sensitive?
I apologize in advance if this is a stupid question.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
是的，这是视图所在服务器上区分大小写排序规则的结果。
这就是为什么我总是抱怨直接在服务器级别使用区分大小写的排序规则（而不是仅在列级别使用排序规则），正如我在这里指出的：
http://www.sqlmag.com /blog/practical-sql-server-45/tsql/Collation-SQL-Server-139576
只是，在尝试将 COLLATE 子句塞入分布式查询中时，我我认为必须有更好的方法。
而且，看起来您可以设置一些特定选项作为链接服务器定义的一部分：
http://msdn.microsoft.com/en-us/library/ms191145.aspx
因为它定义了在分布式查询中使用排序规则时如何指定排序规则选项。
然而，问题是您需要使用链接服务器，而不是允许临时分布式查询（但无论如何，它们是一个安全风险/问题）。因此，无论如何，我建议使用链接服务器作为更好的方法。
也尝试此链接 - 因为它提供了有关如何控制链接服务器上的排序规则设置的详细信息：
http://msdn.microsoft.com/en-us/library/ms186839.aspx
当然，如果可能的话，我建议更改列名称。
- 麦克风
Yes, this is the result of a case-sensitive collation on the server where your views are located.
And it's why I always grouse about having case-sensitive collations at the server level directly (instead of just using collations at the column-level) as I pointed out here:
http://www.sqlmag.com/blog/practical-sql-server-45/tsql/Collation-SQL-Server-139576
Only, in trying to wrap my brain around how you'd cram a COLLATE clause into a distributed query, I figured there had to be a better way.
And, it looks like you can set some specific options as part of your linked server's definition:
http://msdn.microsoft.com/en-us/library/ms191145.aspx
As that defines how to specify collation options when working with Collations in Distributed Queries.
The issue, however, is that you'll need to use a linked server instead of allowing ad-hoc distributed queries (but they're a security risk/concern anyhow). So I'd recommend using a Linked Server as a better approach anyhow.
Try this link as well - as it provides details on how to control collation settings on the linked server:
http://msdn.microsoft.com/en-us/library/ms186839.aspx
And, of course, I'd recommend changing the column name if at all possible.
--Mike
您的列名称需要更改。如果 ENTRYDATE 列是文本且 EntryDate 是日期数据类型，则将 ENTRYDATE 更改为 ENTRYDATE_TEXT。或者将 EntryDate 更改为 EntryDate_Improved。即使您可以让链接服务器视图满意，未来使用它的开发人员也会浪费时间，并且可能会引入错误，因为它非常不清楚。
Your column names need to be changed. If the ENTRYDATE column is text and EntryDate is a Date data type then change ENTRYDATE to ENTRYDATE_TEXT. Or change EntryDate to EntryDate_Improved. Even if you could get the linked server view to be happy, future developers working with this are going waste time and possibly introduce bugs because it is very unclear.