none
OLE DB Provider for ODBC limitations when connecting to MySQL?

    Question

  • Hi,

    I have setup a Linked Server using OLE DB Provider for ODBC to a MySQL 5.2 (?) instance.  I can select from 230 of the 231 tables through the link, but one table fails a query if I include a column which is varchar(32000).   If I leave out that column, the query works. 

    So I was wondering if this is a limitation of ODBC, or perhaps this is a configuration issue?  I can live without  the data, but it would be nice to be able to retrieve this.

    OLE DB provider "MSDASQL" for linked server "MYSQLECC" returned message "Requested conversion is not supported.".

    Msg 7341, Level 16, State 2, Line 1

    Cannot get the current row value of column "[MYSQLECC]...[sql_per_category].sql_string" from OLE DB provider "MSDASQL" for linked server "MYSQLECC".

    Thanks,

    Mike

    Friday, August 02, 2013 4:08 PM

All replies

  • in SQL varchar is max 8000

    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, August 02, 2013 4:27 PM
  • Yes, I am aware of that.  The error appears to be in the ODBC layer, not in the SQL engine. 
    Friday, August 02, 2013 4:36 PM
  • Hello,

    Just a little add-in to the excellent post of Prajesh.

    According the documentation of the MySQL 5.5 ( I found nothing about the 5.2 version , but the 5.5.2 version is existing )

    http://dev.mysql.com/doc/refman/5.5/en/column-count-limit.html

    "Every table (regardless of storage engine) has a maximum row size of 65,535 bytes. Storage engines may place additional constraints on this limit, reducing the effective maximum row size" 

    Moreover , it could happen a problem with the real length of a column which could depend of the collation

    "utf8 characters require up to three bytes per character, so for a  CHAR(255) CHARACTER SET utf8 column, the server must allocate 255 × 3 = 765 bytes per value. Consequently, a table cannot contain more than 65,535 / 765 = 85 such columns" 

    Please , could you tell us whether you are adding a column varchar(32000) to a table defined in your MySQL base ( you may be over the 65,535 bytes limit ?

    Please , could you provide the exact and full name of your OLE DB Provider for ODBC ( version and provider who creates it ) ?

    We are waiting for your feedback to try to help you more efficiently

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    Friday, August 02, 2013 4:56 PM
    Moderator
  • Hi,

    The MySQL is 5.6.12-enterprise from Oracle.

    The driver is "MySQL ODBC 5.2 ANSI Driver" which I think was installed with I installed Oracle's version of MYSQL on my PC.  I know very little about ODBC.  If there is another way to easily suck-out the data I would be open to that, but a Linked Server appeared to be a clean way.

    The table in question is in a vendor's product database and I am not altering or modifying it in any way. I am just trying to suck the data out through a SQL Server 2008 R2 database Linked Server.  While the MySQL column is defined as varchar(32000) this should not prevent the retrieval of the data to SQL Server which is more than able to handle such a small column width. 

    Thank you for your time,

    Mike


    • Edited by Mysetdancer Friday, August 02, 2013 7:01 PM MOre detail
    Friday, August 02, 2013 5:45 PM