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?