Problem querying MySQL Database from a MSSQL Linked Server

Posted on 2011-04-19
Last Modified: 2012-05-11
I'm trying to query a MySQL database via a SQL2008 linked server and am getting the following error:

OLE DB provider "MSDASQL" for linked server "DENEB" returned message "[MySQL][ODBC 5.1 Driver][mysqld-5.0.26]MySQL client ran out of memory".
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT * FROM sitescape.SS_ChangeLogs" against OLE DB provider "MSDASQL" for linked server "DENEB".

My query is as follows:

I can successfully query ANY individual columns from the MySQL source table above with the exception of one which is defined in the MySQL table as a "longtext". This is probably similar to MSSQL's "ntext" or "text".

Even querying just the longtext column alone generates the error.

I need to be able to query the table in it's entirety in effort to bring the MySQL table into a SQL Server equivalent. Any ideas of what I can do to beat/circumvent the error?

The MySQL source table is about 150k rows and sits on a separate server than the MSSQL server I'm querying from.

Thanks much,

Question by:glo-dba
    LVL 15

    Assisted Solution

    by:Walter Ritzel
    You should list in your select statement all the columns of that table, instead of using the *

    Author Comment

    When I reference the binary column directiy in the select statement the same error occurs.
    LVL 15

    Accepted Solution

    Yes, I forgot to mention. You probably dont have how to bring the binary column in a standard select. I would say that for this specific field, you should create some code that will dump the information from this field directly from MySQL into a folder (assuming the content is a file or something like that), and then reload into mssql.

    Again, this is my experience. Maybe someone has other idea.

    Author Closing Comment

    Ultimately casting the binary into a varchar(max) got me around the problem.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now