How To Add MySql As Linked Server In SQL Server

How To Add MySql As Linked Server In SQL Server

In this post we will learn How To Add MySql As Linked Server In SQL Server.I have already discussed about LINKED SERVER.

A linked server (a virtual server) may be considered a more flexible way of achieving remote access, with the added benefits of remote table access and distributed queries. Microsoft manages the link mechanism via OLE DB technology. Specifically, an OLE DB data source points to the specific database that can be accessed using OLEDB.

1. Create ODBC DSN for MySQL.Download and install latest MySQL Connector/ODBC Drivers 5.2.6 from http://dev.mysql.com/downloads/connector/odbc/.

2. Once you download and install the ODBC drivers, it’s time to create the DSN. Initially check if the drivers are listed under your data sources from CONTROL PANEL>Administrative Tools>Data Sources(ODBC).

3. A window will open, click on Add.New window will pop up, Select MYSQL ODBC 5.2 Driver and click on finish.

4. On Click on Finish a new window will open fill details on this windows as shown below.

5. As per above image, input user, password details of MYSQL and select the database you want to use.Click on OK, and close the window.

6.Now open SQL Server Management Studio.Expand Server objects and right click on linked Server and click on new Linked Server.Fill the details as told below.

Provider:DRIVER={MySQL ODBC 5.2 Driver};SERVER=localhost;PORT=3306;DATABASE=test; USER=root;PASSWORD=;OPTION=3;

Product Name:MySQL

Data Source: MySQL

Linked Server:MYSQL

7. On Security Tab Check the Be made using the security context, and fill the MYSQL user and Password.

8. On Server Option Set RPC OUT to TRUE.

9. Now Run the following query and you will get the results.

select * from openquery(MYSQL,'select * from demo')