MySQL As Linked Server

Introduction

As a develeper and DBA, sometimes you need to deal all kinds of databases. We have been getting a lot of inquiries and requests to make MySQL OLE DB Provider capable for developers and/or DBAs like you to setup MySQL as linked server in MS SQL Server. After reading this page, we hope you will know:

• How to setup MySQL as linked server in SQL Server 2005
• How to select/insert/delete/update MySQL from SQL Server

Setup Linked Server

If you have done some kind of linked server setup, it shall be easy for you. Here are some steps you need to follow in order to setup MySQL as Linked Server using MySQL OLE DB Provider.

If you have done some kind of linked server setup, it shall be easy for you. Here are some steps you need to follow in order to setup MySQL as Linked Server using MySQL OLE DB Provider. Install MySQL OLE DB Provider on SQL Server 2005 database server Activate the provider you just installed Open Microsft SQL Server Management Studio and connect to SQL Server 2005 In the Object Explorer, expend Server Objects (F1) Right click on Linked Servers, select New Linked Server, you see the New Linked Server form (F2)

F1. Microsoft SQL Server Management Studio

• 
• Fill in Linked Server name with 'MYLINKEDSVR'
• Select Other data source as Server Type
• Select 'MySQL Provider' as Provider
• Fill in MySQL Server Name or IP address as Data Source
• Fill Provider String with 'User ID=root;Password=mySecret;'
• Ffill Catalog with 'test' - the database name in MySQL
• On upper left conner of this form (F2), click Security, you will see the linked server Security form (F3)
• 
• At the bottom of F3, select Be made using this security context
• Fill in 'root' as remote login and 'mySecret' as passord
• Click OK to added this new linked server
• One last thing to do is to grant permission by running the following SQL script against SQL Server 2005:

EXEC master.dbo.sp_MSset_oledb_prop N'OleMySql.MySqlSource', 'AllowInProcess', 1 GO

Query MySQL As Linked Server

Select Statement

select * from openquery(MYLINKEDSVR, 'select * from tb_alltypes')

Insert Statement

insert openquery (MYLINKEDSVR, 'select id, name, address from cust where id=0')

values(6300, 'Greg Oden', 'Rose Garden, Cherry City, OR')

Delete Statement

delete from openquery(MYLINKEDSVR, 'select id, name, address from cust') where id=4200

Update Statement

update openquery(MYLINKEDSVR, 'select id, name, address from cust order by id')

set id = 6001 where id=3201

Limitation

The current implementation regarding linked server works with MySQL 5.0 or newer. There is no plan to make it also work with MySQL 4.1 or older.
To delete/update records, the select statement inside openquery must include either primary key or unique index

---