Cannot create link from SQL Server to MYSQL

[rmurgia]

I created a System DSN called RunzLink. It tested successfully and I was able to create a linked table in MS Access with it. I created the following linked server in SQL Server:

EXEC sp_addlinkedserver   'RunzLink',  'MySQLDatabase',  'MSDASQL', 'ikonnect.ikon.org'
EXEC sp_addlinkedsrvlogin 'RunzLink', 'FALSE',NULL, 'UserName', 'PW'

I got the following error:
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "RunzLink".

Any Ideas why it would work in MS Access but not SQL Server?

[ck9663]

Check sample B from here

Good Luck!!!

~~ CK

[rmurgia]

Thanks for the response. I think the code you suggested must be a variation of what I was using. I tried it and got the same message. When I set up a successful link for Oracle, I had to set something up in an Oracle file called tnsnames.ora. Could there be some such equivalent requirment to link to MYSQL?

[Miko Genetz]

You can check the a sample connection string for Mysql here: ozekisms.com/index.php?ow_pa ge_number=168 MySQL create table script can be also found here.

BR, Miko