How to create MySQL Link Server in SQL Server 2012

Workbench is a free tool for MySQL but it is collection of issues in working environment. So I came back on SQL Server management studio. But think if our requirement is something like what , data will be at same place in MySQL and we have to access from SQL Server. Then we can use SQL Server's  Server object’s Link server. Today I am going to share 7 steps how I have made linked server from SQL Server to MySQL. 

1^st Ensure that we have MySQL Connector/ODBC 5.2 is installed. To verify check Control panel, if it is not then download and installed.

2^nd Now open ODBC Connection Manager Window from control panel/Administrator Tool and ODBC Data Source. I have been selected 64 bit because my system is 64 bit platform.

3^rd Now Add New Data Source Name, Server name, Port and Credentials. Then Check by Test option, is everything is all right. If all is OK then click on ok and just close this window.

4^th Now Go to SQL Server and drill-down the Server object. We will see Linked Server. Just right click and choose New Linked Server.

5^th Now Create Link server by Filling required information like below: give appropriate server name, provider as given, Product name, Data Source Name and Provider string and we can also give catalog. Linked Server name I have given XXX and Data-source name will be XYZ, which i gave created earlier from ODBC Connection manager.

6^th Now give the Remote Login and Credential in Next Tab of window.

Or 

we can also use below script to create Linked Server

USE [master]

GO

/****** Object:  LinkedServer [XXX]    Script Date: 26-11-2014 15:20:54 ******/

EXEC master.dbo.sp_addlinkedserver @server = N'XXX', @srvproduct=N'MySQL', @provider=N'MSDASQL', @datasrc=N'xyz', @provstr=N'DRIVER={MySQL ODBC 5.2 Driver};SERVER=xx.xx.1.xx;PORT=3306;DATABASE=xxxxxx; USER=suman;PASSWORD= Blue@123;OPTION=3;', @catalog=N'xxxx'

 /* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'XXX',@useself=N'False',@locallogin=NULL,@rmtuser=N'suman',@rmtpassword='########'

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

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

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

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

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

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

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

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

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

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

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

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

EXEC master.dbo.sp_serveroption @server=N'XXX', @optname=N'remote proc transaction promotion', @optvalue=N'true'

7^th After that we will see the Linked server created as below …

8^th Now run below query to select record from sql server to mysql using link server.

select * from openquery(xxx,'select * from DB.TABLE')

#yoyo MySQL Data in SQL Server….