glo-dba
asked on
Problem querying MySQL Database from a MSSQL Linked Server
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]MySQ
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:
SELECT * FROM OPENQUERY(DENEB, 'SELECT * FROM sitescape.SS_ChangeLogs')
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,
Mike
OLE DB provider "MSDASQL" for linked server "DENEB" returned message "[MySQL][ODBC 5.1 Driver][mysqld-5.0.26]MySQ
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:
SELECT * FROM OPENQUERY(DENEB, 'SELECT * FROM sitescape.SS_ChangeLogs')
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,
Mike
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ultimately casting the binary into a varchar(max) got me around the problem.
ASKER