Cannot create linked server

I posted this in the Data Access forum already - may have been the wrong place...

I cannot create a linked to a MySql database in Enterprise Manager. Here is the relevant info:

Trying to create a linked server on a SQL Server 2000 Standard Edition Service Pack 4 running on a Windows 2000 Standard Edition server.

The MySQL server is running on Red Hat - not sure of the DB or OS version. However the DSN I created connects to the server correctly. I can connect using any of the MySQL tools.

I am using the latest ODBC MySQL drive 3.51...Here is the Provider string I am using...

DRIVER={MySQL ODBC 3.51 Driver} ;SERVER=192.168.1.99;DATABASE=bug_tracker; USER=UserID; PASSWORD=Password; OPTION=3

I have also tried using a System DSN to connect with, but that does not work either. It appears to create the linked server correctly each time, but I cannot access any of the tables. Here is the error message I get each time:

Error 7399: OLE DB Provider 'MSDASQL' reported an error. Datasource name not found and no default driver specified] OLD DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ]

Sure could use a little help!

Thanks!!!!

Brad Feaker

Please reply to forum only! Thanks!

Seems like no one else knows jack about this either!
|||

try this

Go to security/Linked Servers
Right Click on Linked Servers--> New Linked Server

Go to security/Linked Servers
Right Click on Linked Servers--> New Linked Server

Use Microsoft OLE DB Provider for ODBC Drivers

Use the Connection String
DRIVER={MySQL ODBC 3.51
Driver};SERVER=myserver.com;DATABASE=database;USER
=user;PASSWORD=password;OPTION=3

And in Provider Options select:

level zero only
Non-transacted updates (something)
Allow InProcess

let me know if you still get an error

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Still getting the same error :-( BTW - I bookmarked your blog. You have some good stuff there. Thanks for the effort. I will keep trying - Almost no experience with MySQL...

Brad
Ex Nihilo, Nihil Fit
|||

Do you have the MySQL Connector/ODBC 3.51 installed?

get it here-->http://dev.mysql.com/downloads/connector/odbc/3.51.html

also from a query window, does this work for you?

SELECT a.*
FROM OPENROWSET('MSDASQL',
'DRIVER={MySQL ODBC 3.51 Driver};SERVER=192.168.1.99;DATABASE=bug_tracker;user=UserID;PASSWORD=Password;OPTION=3',
TestDB ) AS a

change TestDB to your table name

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Yes - I have the latest driver installed - the OPENROWSET query give me the same error message as the linked server. I can open the MySQL Query Browser, connect and run queries with no problem. I just cannot seem to get a linked server to work with SQL Server 2000. This is really frustrating. If I can't get this set up, I am going to have to use DTS and write some real heavy-duty scripts as these tables are going to feed a data warehouse in Analysis Services. It also makes me feel pretty stupid because I cannot get it to work. Back to the drawing board and thanks for your help.

Brad
Ex Nihilo, Nihil Fit
|||

The driver is installed on the SQL server box right?

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Got it fixed finally...Thanks for everyone's help!
|||

I am having a similar problem with SSIS pulling data from MySQL

How did you get it fixed?

Thanks

-Raghu Raman

|||

Hi everybody, I have the same problem that has been treated here, but none of the solutions offered has been good for me.

Does anybody have any help respect to the problem with the linked server?

Thanks, blessings.