Create A Linked MySQL To SQL Server

While working on one of my projects, I came across a scenario where I had a database of the client's that was provided by some other provider through their application. I had to get data from that database, and using that data I had to do some calculations on that and store the results in my database. There were two scenarios in this case:

I could fetch all the required tables and data from that database and create my own database and dump those details into mine and then use it.
I could create a link between my database and the existing database, so that I could fire queries directly on that database.

Using the first approach will not give me updated records at any point of time but my scenario was real time data. So I thought of using a second approach. This approach gave me real time data as I was querying the existing data and using it. But there is one drawback: It might be somewhat slow. In this article I will explain how to create a linked server in SQL Server. Here I will be using ODBC Driver to fetch data from MYSQL database.

The following are the steps to connect a MySQL database to a SQL Server:

First of all we need to install the appropriate MySQL ODBC Driver based on the operating system from the below link. After the driver has been installed go to Control Panel, Administrative Tools, then Data Sources(ODBC). After that click System DSN. Now Press Add Button .

Select MYSQL Driver Listed(MYSQL(ODBC) 5.3 ANSI Driver) and click finish. This will open up MySQL Configuration window.

Fill Data Source Name as MYSQL (this can be anything). TCP/IP Server as localhost. Port as 3306(default port for mysql), User Name-root, Password -your database password and click test. This will show a success message. Now select database and click ok.

We are done with MYSQL System DSN. Now we will set Linked Server to MYSQL in SQL Server. Open SQL Server, Server Objects, then Linked Server. Right Click on Linked Servers, then Add New Linked Server.

This will Open up a Linked Server Properties Dialog. Fill Linked Server as MYSQL_LINKED, select Provider as Microsoft OLEDB Provider For ODBC Drivers. Product Name as MySQl, DataSource as MySQL_Linked (whatever name is given while creating DSN). Provider String as,
1. DRIVER=(MySQL ODBC 5.2 ANSI Driver);SERVER=localhost;PORT=3306;DATABASE=databasename; USER=username;PASSWORD=password;OPTION=3;

Leave location as blank and Catalog as database name (in mysql).

Drill down to Server Object, then Linked Servers, Providers, right-click MSDASQL, and select “Properties”. The Provider Options for Microsoft OLE DB Provider for ODBC Drivers dialog box will open allowing you to configure several options. Ensure the following four options are checked:

Nested queries

Level zero only

Allow inprocess

Supports ‘Like’ Operator

All other options should be unchecked. When done, click “OK”.

In addition to this, you can enable provider options on the SQL OLEDB, In my case I select the Dynamic Parameter and Allow in process.
We are done with setting up a linked server. Now we have to test it by firing some basic queries. There are three ways by which we can query a linked server.

Open Query
Select using 4 part notation.
Execute Function

Open Query function requires 2 parameters: 1)Linked Server Name, 2)Query

select * from openquery (MYSQL_LINKED, 'select * from test.user_details');
INSERT OPENQUERY (MYSQL_LINKED, 'select name,address from test.user_details') VALUES ('Rajeev','Bangalore');
UPDATE OPENQUERY (MYSQL_LINKED, 'select name from test.user_details WHERE user_id = 100006413534648') SET name = 'Akash';
DELETE OPENQUERY (MYSQL_LINKED, 'select name from test.user_details WHERE user_id = 100006413534648')

Note:

For Update/Delete on Linked Server we need to set RPC and RPC OUT properties of Linked Server as true (Right click Linked Server, Properties, Server Option Tab, RPC-True, then set RPC OUT -True.

Part Notation: We can also execute queries on linked server using four-part notations like:

SELECT * FROM linkedserver...tablename but for this we need to change MSDASQL Provider property. Check the box that says “level zero only” in providers.

select * from MYSQL_LINKED...user_details
INSERT into MYSQL_LINKED...user_details(name,address) VALUES ('Rajeev','Bangalore');
UPDATE MYSQL_LINKED...user_details set name='Akash' where USER_ID='100006413534649';
DELETE from MYSQL_LINKED...user_details where USER_ID='100006413534649';

Execute Function can also be used for querying linked server.

EXECUTE('delete from test.user_details WHERE user_id = 100006413534647') AT MYSQL_LINKED