How To Create Linked Server From MySQL TO SQL SERVER

Step by step process for to create linked server from MySQL TO SQL Server.
 
Step 1: Go To Control Pnel ->Addministrative Tool ->Data Source(ODBC) ->Double Click ->Open SYStem DSN tab ->Click On Add ->Fill Information.

 

Step 2: Goto SQL SERVER ->Server Object ->Linked Server ->Right Click ->New Linked Server ->Fill Information ->

Select OLEDB For ODBC as given in picture

Please Be careful on Provider String
 
Provider String=ODBC;DSN=MYSQLLINK
 
Put the Datasource same as named when creating ODBC Connection(Data Source name). Now Click OK
 
Step 3: Your Linked Server has been created. Now test it by given query.
 
To show the data from a table in MYSQL database use below query syntax.
 
select * from openquery(LINKED_SERVE_NAME,'select * from mysqltable')
 
Example
 
select * from openquery(TEST,'select * from mysql_tbl_name')
 
To insert SQL Server table data to mysqlserver table, use given query
 
insert OPENQUERY(LINKED_SERVE_NAME, 'select field_list from MYSQL_TABLE') 
select field_list  from SQL_TABLE
 
Example
 
insert OPENQUERY(TEST, 'select name,add,lname from mysql_user') 
select  f_name,address,l_name from SQL_USER
 
NOTE - By SQL Query we also can create Linked Server after ODBC Connection created .
Query is given below.

/****** Object:  LinkedServer [TEST]    Script Date: 05/08/2013 17:32:45 ******/

EXEC master.dbo.sp_addlinkedserver @server = N'TEST', @srvproduct=N'TEST', @provider=N'MSDASQL', @datasrc=N'MYSQLLINK', @provstr=N'ODBC;DSN=MYSQLLINK'

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

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