sql server odbc: Hi folks,

I'm having a spot of bother creating a linked MySQL server on SQL2K.

I'm using the following code on an SP4 SQL2K instance. MySQL 5.1 is
installed on the same box, and I've installed the MySQL ODBC 3.51 Driver.

sp_addlinkedserver
'myAlias'
, 'MySQL'
, 'MSDASQL'
, Null
, Null
, 'Driver={MySQL ODBC 3.51
Driver};DB=myDatabase;UID=myUserName;PWD=myPassword;SERVER=localhost;'
go

EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'myAlias',
@useself = 'TRUE',
@locallogin = NULL
GO

-- List the tables on the linked server
EXEC sp_tables_ex 'myAlias'
GO

sp_tables_ex throws the following error:

OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: The provider did not give any information about the
error.].
Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 13
OLE DB provider 'MSDASQL' reported an error. The provider did not give any
information about the error.


BUT if I use the exact same syntax on a SQL2005 server, it works just fine.

Any ideas?

I'm not sure about a fix for the specific issue you mention above but
i've found this post providing details on making the connection via ODBC
between MSSQL2000 and MySQL.

http://www.sqlservercentral.com/Forums/Topic340912-146-1.aspx

Hope this helps.

Paul

Thanks for that Paul - this looks useful!

Pete

[quoted text, click to view]