server to MYSQL using OLEDB Provider for MYSQL cherry

Good Morning

Has anyone successfully used cherry's oledb provider for MYSQL to create a linked server from MS SQLserver 2005 to a Linux red hat platform running MYSQL.

I can not get it to work.

I've created a UDL which tests fine. it looks like this

[oledb]

; Everything after this line is an OLE DB initstring

Provider=OleMySql.MySqlSource.1;Persist Security Info=False;User ID=testuser;

Data Source=databridge;Location="";Mode=Read;Trace="""""""""""""""""""""""""""""";

Initial Catalog=riverford_rhdx_20060822

Can any on help me convert this to corrrect syntax for sql stored procedure

sp_addlinkedserver

I've tried this below but it does not work I just get an error saying it can not create an instance of OleMySql.MySqlSource.

I used SQL server management studio to create the linked server then just scripted this out below.

I seem to be missing the user ID, but don't know where to put it in.

EXEC master.dbo.sp_addlinkedserver @.server = N'DATABRIDGE_OLEDB', @.srvproduct=N'mysql', @.provider=N'OleMySql.MySqlSource', @.datasrc=N'databridge', @.catalog=N'riverford_rhdx_20060822'

GO

EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'collation compatible', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'data access', @.optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'dist', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'pub', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'rpc', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'rpc out', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'sub', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'connect timeout', @.optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'collation name', @.optvalue=null

GO

EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'lazy schema validation', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'query timeout', @.optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'use remote collation', @.optvalue=N'false'

Many Thanks

David Hills

Have you tried to include password to initstring?|||No I have not, as there is no password set for testuser in the mysql database.|||

---

Have you tried to include user id like this @.UID='<my id>'? It shall work with MySQL OLE DB Provider|||

I got a reply from the software provider "cherry" they told me it won't work with

sqlserver 2005 as a linked server.

It should be quite straight forward to write a .net vb applet that uses the .net provider for mysql to

get the data out of mysql server, then use ado.net to write it into sqlserver 2005.

But what I wanted to do is to contain the code within sqlserver management studio so I don't

have external code.

Anyone know if I can write a VB.net or C## .net applet from with sqlserver 2005. It seems it's

the sort of intergrated solution that would be convient to be able to do?

|||I've setup a MySQL linked server in SQL 2005 using the ODBC driver for MySQL, and then using the OLEDB Provider for ODBC. Would you be apposed to doing it that way?