“不支持请求的转换".使用链接服务器和ODBC [英] "Requested conversion is not supported" using a linked server and ODBC

问题描述

我们的Web应用程序将UTF-8编码的数据存储在VARCHAR字段中.最近，我们为客户提供了使用DataDirect的OpenAccess ODBC驱动程序通过ODBC访问此数据的功能.这是通过使用DataDirect的OpenAccess SDK并编写一个C#.Net类与服务进行接口来实现的.我们仅允许客户执行SELECT查询.我们目前也将结果限制为10万行.

Our web application stores UTF-8 encoded data in VARCHAR fields. Recently, we have provided our customers access to this data via ODBC using DataDirect's OpenAccess ODBC driver. This is achieved using DataDirect's OpenAccess SDK, writing a C# .Net class to interface with the service. We only allow customers to perform SELECT queries. We also limit results to 100K rows at this time.

此解决方案确实非常有效，除了可以理解的是，使用某些编码数据查询字段对于用户来说是胡言乱语.在我们的下一版服务中，我希望为用户提供使用未编码的字符串进行查询的功能，并随后查看未编码的结果.

This solution really works great, except querying fields with some of this encoded data appeared as gibberish to the users, understandably. In the next release of our service, I would like to offer users the ability to query using unencoded strings, and subsequently see the unencoded results.

我已经通过对传入查询进行UTF-8编码来解决此问题，然后通过标记VARCHAR字段返回未编码的结果是WVARCHAR.这实际上工作得很好.但这确实意味着，尽管存在(或缺乏)Unicode字符，每个VARCHAR列都作为WVARCHAR返回.

I have solved this by UTF-8 encoding the incoming query, then returning the unencoded results by flagging VARCHAR fields was WVARCHAR. This is actually working really well. It does mean, though, that every VARCHAR column comes back as a WVARCHAR despite the presence (or lack of) Unicode characters.

目前，我们的许多客户已采用在自己的SQL Server实例上的SSMS中创建链接服务器的方法，这是我首选的连接方法.由于我们的服务将结果限制为10万行，因此我鼓励所有人使用OPENQUERY来执行查询.但是，似乎在SSMS中我的链接服务器的配置中缺少某些内容.对这些WVARCHAR列执行字符串函数(例如，LEFT，RIGHT，SUBSTRING)时，SSMS返回以下错误:

Many of our customers at this time have adopted the method of creating a linked server in SSMS on their own SQL Server instance, and it is my preferred method of connecting. Since our service limits results to 100K rows, I encourage everyone to use OPENQUERY to perform queries. It seems, though, that there is something I'm missing in the configuration of my Linked Server in SSMS. When string functions (LEFT, RIGHT, SUBSTRING for example) are performed against these WVARCHAR columns, SSMS returns the following error:

链接服务器"LOCAL"的OLE DB提供程序"MSDASQL"返回了消息 不支持请求的转换.".消息7341，第16级，状态2， 第1行无法获取列"[MSDASQL] .ColName"的当前行值 来自链接服务器"LOCAL"的OLE DB访问接口"MSDASQL".

OLE DB provider "MSDASQL" for linked server "LOCAL" returned message "Requested conversion is not supported.". Msg 7341, Level 16, State 2, Line 1 Cannot get the current row value of column "[MSDASQL].ColName" from OLE DB provider "MSDASQL" for linked server "LOCAL".

对于诸如此类的查询，将返回此值:

This would be returned for a query such as this:

SELECT *
FROM OPENQUERY([LOCAL], '
  SELECT LEFT(FirstName, 2) AS ColName
  FROM dbo.User 
')

如果要从此查询中删除LEFT函数，则将返回FirstName列，并对其进行正确解码，而不会出现错误.

If I were to remove the LEFT function from this query, the FirstName column would be returned, properly decoded, without error.

例如，此问题不会影响在MS Excel中的查询.从表面上看，当我通过与DataDirect产品接口的.Net类调试时，字符串似乎受其各自功能的影响.我试图更改链接服务器"属性上的所有服务器选项"，但没有找到合适组合的运气.我只是在寻找合适的树皮在这里树皮.是我对结果的处理，将其更改为WVARCHAR吗?还是我需要更改丢失的SSMS链接服务器的某些属性?

This problem does not affect queries in, say, MS Excel for example. And on the surface the strings seem to be properly affected by their respective functions as I debug my way through the .Net class which interfaces to the DataDirect product. I've attempted to change all the Server Options on the Linked Server properties, but I've not had any luck finding the right combination. I'm just looking for the right tree to bark up here. Is it my treatment of the results, changing them to WVARCHAR? Or is it some attribute of my SSMS linked server that I need to change that I'm missing?

推荐答案

在链接服务器属性上，将COLLATION COMPATIBLE设置为TRUE，即可解决问题.

On linked server properties, on COLLATION COMPATIBLE set to TRUE, the problem is resolved.

这篇关于“不支持请求的转换".使用链接服务器和ODBC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！