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.

Image1.jpg

Image2.jpg

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

Image3.jpg

Select OLEDB For ODBC as given in picture

Image4.jpg


Image5.jpg

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