Problem with SELECT * in MySQL through ODBC from Microsoft SQL Server

Question

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')

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

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

raises an error:

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.

How can I make the SELECT * syntax work?

Answer 1

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

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.

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).

This will work with no problem.

Please let me know if this fixed it.

Tarek Basta

Answer 2

Executing the following command before queries seems to help:

DBCC TRACEON(8765)

The error messages go away and queries seem to be working fine.

I'm not sure what it does though; I found it here: http://bugs.mysql.com/bug.php?id=46857

Strangely, SQL Server becomes unstable, stops responding to queries and finally crashes with scary-looking dumps in the logs a few minutes after several queries to the MySQL server. I am not sure if this has to do anything with the DBCC command, so I'm still interested in other possible solutions to this problem.