Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Linked Server (MYSQL) links ok, but cannot see tables and queries return errors Expand / Collapse
Author
Message
Posted Tuesday, May 17, 2011 9:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 25, 2013 11:33 AM
Points: 18, Visits: 211
I followed the steps in this article and successfully got a Linked Server on SSMS.
http://www.ideaexcursion.com/2009/02/25/howto-setup-sql-server-linked-server-to-mysql/

The database is there, but no tables are visible. Aren't I supposed to be able to view the tables, just like regular SQL Server tables can be viewed? None are listed.
----------------------
I also ran some code manually to get info on a table:

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

and got this error:

An unexpected NULL value was returned for column "[MSDASQL].checked_out_time"
from OLE DB provider "MSDASQL" for linked server "MYSQL". This column cannot be NULL.
Post #1110326
Posted Tuesday, May 17, 2011 12:10 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:51 PM
Points: 7,140, Visits: 12,763
mariann harper (5/17/2011)
I followed the steps in this article and successfully got a Linked Server on SSMS.
http://www.ideaexcursion.com/2009/02/25/howto-setup-sql-server-linked-server-to-mysql/

The database is there, but no tables are visible. Aren't I supposed to be able to view the tables, just like regular SQL Server tables can be viewed? None are listed.

----------------------
I also ran some code manually to get info on a table:

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

and got this error:

An unexpected NULL value was returned for column "[MSDASQL].checked_out_time"
from OLE DB provider "MSDASQL" for linked server "MYSQL". This column cannot be NULL.

Both issues are likely due to the fact that the version of MySQL database your connecting to does not contain an implementation of INFORMATION_SCHEMA consistent with ISO standards. It could also be a driver issue.

What version of MySQL are you connecting to? What version of the MySQL ODBC driver are you using?


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1110483
Posted Tuesday, May 17, 2011 1:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 25, 2013 11:33 AM
Points: 18, Visits: 211
I'm not sure what version of MYSQL it is, as it's coming from an external website.

I'm using the MySQL ODBC 5.1 driver.
Post #1110547
Posted Tuesday, May 17, 2011 1:36 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:51 PM
Points: 7,140, Visits: 12,763
What does this return when executed on your SQL Server?

EXEC('SHOW VARIABLES LIKE "%version%";') AT [MYSQL];



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1110557
Posted Tuesday, May 17, 2011 2:13 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 25, 2013 11:33 AM
Points: 18, Visits: 211
Variable_name Value

innodb_version 1.0.13
protocol_version 10
version 5.1.52
version_comment MySQL Community Server (GPL)
version_compile_machine i686
version_compile_os pc-linux-gnu
Post #1110580
Posted Tuesday, May 17, 2011 2:26 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:51 PM
Points: 7,140, Visits: 12,763
Thanks. You won't be able to browse your complete MySQL object hierarchy through the SSMS Linked Server node for the reason I mentioned in my previous post about them not coding up to ISO standards but you should be able to pull data. I think all you need to do is start qualifying your table names, like this:

SELECT * FROM OPENQUERY (MYSQL, 'SELECT * FROM SchemaName.Mytable')



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1110587
Posted Tuesday, May 17, 2011 2:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 25, 2013 11:33 AM
Points: 18, Visits: 211
Thanks for your reply.

Unfortunately, I get another error:

Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL" for linked server 'MYSQL'.

Could it be a permissions issue from the website's MySQL setup?
Post #1110594
Posted Tuesday, May 17, 2011 2:59 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:51 PM
Points: 7,140, Visits: 12,763
It's possible that SQL Server is trying to use INFORMATION_SCHEMA on the MySQL to expand the * to an explicit column list under the covers and it's failing due to the lack of an ISO standard INFORMATION_SCHEMA. Try replacing "SELECT *" in your query with an explicit column list. Please post your exact query in your next post if you continue to have issues.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1110606
Posted Tuesday, May 17, 2011 3:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 25, 2013 11:33 AM
Points: 18, Visits: 211
I'd try that, but cannot look at the table to find the columns, lol. It's a catch-22.
Post #1110618
Posted Tuesday, May 17, 2011 3:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:51 PM
Points: 7,140, Visits: 12,763
You can try retrieving the column names from INFORMATION_SCHEMA like this:

EXEC ('SELECT  TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ''MyTable''
AND TABLE_SCHEMA = ''SchemaName'' ;') AT [MYSQL] ;



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1110627
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse