Linked Server to Foxpro: successfully

Showing posts with label successfully. Show all posts

Monday, February 20, 2012

Linked 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'

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

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

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

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

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

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

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

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

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

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

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

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?

Linked Server to MySQL

We are trying to do a linked server to MySQL from MS SQL2k. We downloaded MyODBC drivers, setup the system dsn successfully but then SQL errors out using both the GUI and the stored proc to add the linked server to mysql. Does anyone have a good site to reference or any words of advice. An hr or so of google didn't really give up any helpfully information.

Thanks,
DMWAre you setting up a linked server in SQL Server to connect to MySQL?

"and the stored proc to add the linked server to mysql."

If you are trying to connect to SQL Server from MySQL I would try the MySQL forum.

Also, it would help if you provided the error messages.

If you are trying to setup the linked in SQL Server to access MySQL you should have to execute a sp in MySQL.

Also if you reply to this, post your code for adding the linked server.|||should "not"|||I had it working both ways, MySQL to connect to SQL and have a read-only access to a specific view, as well as SQL to have MySQL as a linked server. The only difference I see is I was doing it from MSDE on XP to MySQL on 2003. But the engine is the same for standard/enterprise and MSDE, so I don't see where you can have issues there. Try to post your error, maybe that would clear the mud ;)

Linked Server to DB2

I am trying to define a linked server to an As400 running DB2 and am having no luck. If anyone out there has done it successfully, please let me know how.

Thanks!

Did you ever figure this out? I'm having the same issue.|||

Please try

1) Installed Host Integration 2000 client license on the SQL Server 2000 server
2) Made sure that the TCP/IP DDM Server is running on the AS400
3) All minimum requirements on the server and the host are met for the
provider
4) Verified the connection/catalog/provider strings
5) Verified the user name and password used to connect to the host

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q218590

HTH

|||

I did get this to work. The script to define the linked server is below. Note that my AS400 is named BKL400. It is not the fastest thing in the world but it sure does come in handy in certain situations. If you still have problems, email me with contact information and we can talk.

/****** Object: LinkedServer [BKL400] Script Date: 06/05/2006 17:00:01 ******/

EXEC master.dbo.sp_addlinkedserver @.server = N'BKL400', @.srvproduct=N'Microsoft OLE DB Provider for DB2', @.provider=N'DB2OLEDB', @.datasrc=N'BKL400', @.provstr=N'Data Source=bkl400;User ID=xxxxxxxxx;password=xxxxxxxxx;Initial Catalog=bkl400;Provider=DB2OLEDB;Persist Security Info=True;Network Address=bkl400;Package Collection=QSYS2;DBMS Platform=DB2/AS400', @.catalog=N'BKL400'

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

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

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

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

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

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

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

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

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

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

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

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