Duplicate column name... but only if database is case insensitive

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 开发者_Go百科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.

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

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.