通过Microsoft SQL Server通过ODBC在MySQL中使用SELECT *的问题 [英] Problem with SELECT * in MySQL through ODBC from Microsoft SQL Server

问题描述

我有一个MySQL服务器作为Microsoft SQL Server 2008中的链接服务器.对于链接，我使用MySQL ODBC连接器版本5.1.8.使用OPENQUERY调用查询(我发现执行查询的唯一方法)时，会出现问题.简单查询，例如

I have a MySQL server as a linked server in Microsoft SQL Server 2008. For the link I use MySQL ODBC Connector version 5.1.8. When invoking queries using OPENQUERY (the only way I found of performing queries), problems occur. Simple queries, such as

SELECT * FROM OPENQUERY(MYSQL, 'SHOW TABLES')

做得很好.选择各个列，例如

work fine. Selection of individual columns, e.g.,

SELECT * FROM OPENQUERY(MYSQL, 'SELECT nr FROM letter')

也可以正常工作，但是SELECT *语法不起作用.查询:

works fine as well, but SELECT * syntax does not work. The query:

SELECT * FROM OPENQUERY(MYSQL, 'SELECT * FROM mytable')

引发错误:

信息7347，第16级，状态1，第6行 OLE DB提供程序"MSDASQL"用于链接 服务器"MYSQL"返回的数据 与预期的数据长度不匹配 列"[MSDASQL] .let_nr".这 (最大)预期数据长度为40， 而返回的数据长度为0.

Msg 7347, Level 16, State 1, Line 6 OLE DB provider 'MSDASQL' for linked server 'MYSQL' returned data that does not match expected data length for column '[MSDASQL].let_nr'. The (maximum) expected data length is 40, while the returned data length is 0.

如何使SELECT *语法起作用?

How can I make the SELECT * syntax work?

推荐答案

我经历了同样的问题4天，但最终我找到了原因和解决方法.

I was going through the same problem for 4 days but finally I found the why and how to fix it.

如果您正在查询mySQL链接服务器，并且查询的表的数据类型为char()，则会发生此问题...这意味着固定长度为NOT varchar().当您的固定长度字段的字符串比sql服务器从odbc期望的最大长度短时，就会发生这种情况.

This problem happened if you are quering mySQL linked server and the table you query has a datatype char()... This means fixed length NOT varchar(). This happen when your fixed length field has shorter string than the maximum length that sql server expected to get from the odbc.

修复；转到MySQL服务器，然后将数据类型更改为varchar()并保留其长度，例如... char(10)将其更改为varchar(10).

The fix; go to MySQL server and change the datatype to varchar() leaving the length as it is... Example, char(10) change it to varchar(10).

这将毫无问题.

请让我知道是否解决了这个问题.

Please let me know if this fixed it.

Tarek Basta

Tarek Basta

这篇关于通过Microsoft SQL Server通过ODBC在MySQL中使用SELECT *的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！