How To Create Linked Server From MySQL TO SQL SERVER

This blog defines step by step process for 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