Creating a linked server to a MYSQL database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Creating a linked server to a MYSQL database

Author

Topic

bendertez
Yak Posting Veteran

94 Posts

Posted - 2010-01-29 : 04:41:04

Hello i'm postinmg this again as it is becoming increasingly frustrating.....

I'm trying to link a MYSQL (phpMyAdmin 3.1.1) database (held on a LINUX server) as a linked server through SQL Server 2005.

I have installed all the relevant software - ODBC MYQL drivers (latest version) and have set up an ODBC DNS which seems to connect with no problems. I have also set up the linked server which enables me to see the MYSQL database and expand to the tables, however i can't see any tables, the folder is empty.

This seems to me like its a priovelege issue within MYSQL, howevere all the priveleges seem okay and look like they are setup to see SQL server 2005 user.

Can anyone shed any ligtht on this?

Could it be something to do with it being on a LINUX server?

Thanks in advance.

Kristen
Test

22859 Posts

Posted - 2010-01-29 : 04:53:13

Tables not displaying may not mean anything. Can you run a simple query?

Linked Server is a minefield. I had to set one up to Dataflex ODBC driver a year or so back. It took days ... and I only really stumbled on the answer by trying everything in sequential fashion, and having some luck.

I do find it easier to set up using SQL, rather than the GUI, because I think it provides a chance to consider all the parmeters carefully.

But its the lack of meaningful error messages when things don't work that really wastes the most time ...

I'm sure what you want to do is doable, you jsut need to find someone who has already done it.

I very much doubt that Linux is the issue.

"
I have installed all the relevant software - ODBC MYQL drivers (latest version) and have set up an ODBC DNS which seems to connect with no problems."

Did you do this on the SQL box?

There is a little ODBC Test rig that lets you choose the ODBC DNS and then just type a simple query. That will prove that the SQL Box can "pull" data from the MySQL box.

bendertez
Yak Posting Veteran

94 Posts

Posted - 2010-01-29 : 05:38:57

Hi Kristen

Thanks for the reply

Yes I did this on the SQL box and like I said there didn't seem any problems. I didn't receive any errors and it looks like it 's okay, I can see the linked server and when I open it up the two databases are there which seems like its connected okay. It's just when I open the tables folder that i can't see anything. I don't get any errors though.

Where can I find this ODBC test rig?

Thanks

Dave

Kristen
Test

22859 Posts

Posted - 2010-01-29 : 05:56:57

"It's just when I open the tables folder that i can't see anything. I don't get any errors though."

That may just be that the tables in MySQL are not in a "style" that MS SQL can resolve. I don't see tables when I connect to some LinkedServers.

Try firing a simple query at the LinkedServer:

SELECT * FROM LinkedServer.Database.Owner.TableWithOnlyAFewRows !!

(Don't stick "SELECT TOP 10" or anything fancy in there; that might not be supported by MySQL and/or the ODBC driver)

No telling if MySQL will expose the tables with both a Database and an Owner. With the LinkedServers I have to Oracle I just do:

SELECT * FROM LinkedOracleServer.TableName

Try that first, but the ODBC Test Rig I think came from MS site - dev kit for ODBC IIRC, its just a little standalone Windows EXE that someone @ Microsoft built as a worked example. Sorry, can't remember more than that, but hopefully its somewhere in the ODBC Developers area. If you don't find it ask again and I'll have a proper hunt for it.

bendertez
Yak Posting Veteran

94 Posts

Posted - 2010-01-29 : 08:52:20

Thanks Kristen

Thats worked a treat.

Thanks again.

Dave

Kristen
Test

22859 Posts

Posted - 2010-01-29 : 09:33:56

Just for the record could you elaborate on what you need to query a MySQL table.

Is it LinkedServer.Database.Owner.TableName, or something with fewer "parts" ?

bendertez
Yak Posting Veteran

94 Posts

Posted - 2010-01-29 : 10:05:05

Hi Kristen

The query i used was:

OPENQUERY(MYSQL, 'SELECT * FROM INTHR.STAFF')

MYSQL = ODBC named DSN system data source and the name of the linked server
INTHR = Database name
STAFF = Table name

I have another query on linked servers which is relating to linking servers to another SQL server instance which is quite annoying, I don't know if you can help at all.

Thanks again

Dave

Kristen
Test

22859 Posts

Posted - 2010-01-29 : 12:16:03

Thanks for the info about MSSQL Linked Server to MySQL (full set of Google keyowrds there

"I don't know if you can help at all."

Happy to try ....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources