Connecting to MySQL 4.1.16 from SQL Server 2k5 Standard

Question

Connecting to MySQL 4.1.16 from SQL Server 2k5 Standard

Rich Cowan

Old Hand

Points: 336
More actions
June 1, 2006 at 9:59 am

#153014

I am trying to set up a linked server to a MySQL database. I used to have this set up on SQL Server 2k and it worked just fine. However, since I have upgaded, I can no longer connect.
Has anyone set up a linked server to a MySQL database successfully from SQL Server 2k5?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Shaun Stewart SSC Eights! Points: 890 More actions · Answer 1

Hi Rich,

I recently installed a link to a MySql DBMS running on Windows XP professional and it works just fine. I used the odbc 3.51.12 driver from the MySql Web site.

regards

Shaun

Quis custodiet ipsos custodes.

Rich Cowan Old Hand Points: 336 More actions · Answer 2

Ok, so can you tell me how you set up the MySQL account? What is currently happening is that I can set up the ODBC using the same version. It successfully connects when tested. However, when I attempt to query through the linked server, it practically hangs SQL Server Management Studio.

Here is how I am using the linked server

SELECT * FROM OPENQUERY(LINKEDSERVERNAME,'SELECT * FROM sys;')

Now, this used to work. However, we have upgraded both SQL Server from 2k Standard to 2k5 standard and we upgraded MySQL from 4.1.8 to 4.1.16.

Any help would be greatly appreciated.

Thanks!

R.

Shaun Stewart SSC Eights! Points: 890 More actions · Answer 3

Rich,

The following works fine in my system.

SELECT

* FROM OPENQUERY(MYSQLTEST,'SELECT * FROM categories;')

One of the things that I noticed was that my MySQL installation is case sensitive.

The following is my linked server setup for MYSQLTEST

General: Linked Server: MYSQLTEST

Provider: Microsoft OLEDB Provider for ODBC drivers

Product name: MySqlDatabase

Data Source: MySqlTest (A system DSN)

Security

Remote user: root

Password: *********

Connection to be made without a security context

(I know root is not recommended but I wanted the path of least resistance for the initial test) the user name has now been changed.

Server Options

Data access: true

use remote collation: true

All the rest are false or zero.

One thing that you might try is using sp_addlinkedserver and

sp_addlinkedsrvlogin (see BOL). I once encountered a problem connecting to and Informix database using the wizard but the SPs appear to have done the trick.

Hope this helps

Shaun

Hope this has been of some assisstance

Quis custodiet ipsos custodes.

Shaun Stewart SSC Eights! Points: 890 More actions · Answer 4

Rich,

I have just noticed that we now actually have version 5.n of MySql rather than version 4.n. It might be worth making a test installation and seeing if that works.

regards

Shaun

Quis custodiet ipsos custodes.

Rich Cowan Old Hand Points: 336 More actions · Answer 5

Shaun, how is your ODBC set up. did you use DNS name of box or the IP?

I went through and set up my linked server the same as yours and I still have the same problem. I pretty much had it set up that way before except that I had MySQL instead of MySQLDatabase in the product. Are you supplying the location or the cataloge to the Linked Server definition?

Thanks!

Rich Cowan Old Hand Points: 336 More actions · Answer 6

ok, Shaun, I know that my ODBC works because I can get data through Excel.

However, when attempting to set up a linked server using Access or SQL Server 2k5, I get nothing but hung system (actually crashes Access 2k3).

Not too sure where to go from here. Thanks for your help though.

R.

Shaun Stewart SSC Eights! Points: 890 More actions · Answer 7

Rich,

I just tried linking the MySql database from Access 2002 SP3 and that worked fine too.

I wish I could be of more help!

Shaun

Quis custodiet ipsos custodes.