May 17, 2011 at 9:44 am
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.:hehe:
May 17, 2011 at 12:10 pm
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.:hehe:
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
May 17, 2011 at 1:26 pm
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.
May 17, 2011 at 1:36 pm
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
May 17, 2011 at 2:13 pm
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
May 17, 2011 at 2:26 pm
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
May 17, 2011 at 2:34 pm
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?
May 17, 2011 at 2:59 pm
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
May 17, 2011 at 3:25 pm
I'd try that, but cannot look at the table to find the columns, lol. It's a catch-22.
May 17, 2011 at 3:41 pm
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
May 17, 2011 at 3:59 pm
When I run either of the scripts below, I find the table named jos_contact_Details, which is in the database named tctl_01.
EXEC sp_tables_ex 'MYSQL'
SELECT * FROM OPENQUERY
(MYSQL, 'SELECT * FROM INFORMATION_SCHEMA.TABLES')
When I run scripts below, i get nothing returned. I also tried naming the table with the
database, like tctl_01.jos_contact_Details":
EXEC ('SELECT TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ''jos_contact_Details'';')
AT [MYSQL]
EXEC ('SELECT TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ''jos_contact_Details'';')
AT [MYSQL]
May 17, 2011 at 4:20 pm
I think we're losing some traction...do you have the MySQL command-line client on your machine? It might be easier for you to log into the MySQL instance directly, build your working queries there, and then transfer them into your OPENQUERY commands in SQL Server.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 17, 2011 at 4:22 pm
I think I do, it was years ago. Not sure what you mean by connecting and then query over to sql?
May 17, 2011 at 4:28 pm
I only mean to say that working through a Linked Server to design your MySQL queries when you don't have the full complement of tools available to discover table and column names is not very productive.
If you install the MySQL command-line client you can then connect directly from your workstation to the MySQL instance. You can design your queries using the MySQL client and once they deliver the data you like simply copy and paste the SQL into your OPENQUERY commands for use within SQL Server.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply