Cannot create linked server

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!

Try:

EXEC sp_addlinkedserver 'mysql', '', 'MSDASQL', 'your_system_dsn'

EXEC sp_addlinkedsrvlogin 'mysql', 'false', null, 'userid', 'pwd'|||Sorry - have already tried that too. Same error message.

Thanks for your help!

Brad
Ex Nihilo, Nihil Fit|||

There are two possibilities:

1. Incorrect provider-string
2. Incorrect linked server setup

To isolate the issue, try to use the provider string with OPENROWSET syntax. If this works, then try to create a new linked server definition with appropriate login mappings.

|||

Brad,

Sorry, I do not use mySQL so cannot be of much help as far as the correct provider/library.

However, this KB looks like a solution to your problem.

http://support.microsoft.com/default.aspx?scid=kb;en-us;814398