Creating linked server to MySQL from SQL server or Setup SQL Server Linked Server to MySQL

DNS: - Domain Name System

Step 1:- Download Connector/ODBC 

https://dev.mysql.com/downloads/connector/odbc/

Step 2:- GO TO

Start => Control Panel => System & Security => Administrative Tools => Data Sources (ODBC)

Step 3: GO TO

System DNS => Click on Add Button => Select MySQL ODBC 5.3 Unicode Driver from List => Click on Finish Button => Fill Details => Click on Test Button => Click on OK Button => Click on OK Button

Step 4:- Step by Step

Start

Control Panel

System & Security

Administrative Tools => Click on Data Sources (ODBC)

Select System DNS => Click on Add Button

Select MySQL ODBC 5.3 Unicode Driver from List => Click on Finish Button

Fill Details

Click on Test Button => Click on OK Button to close popup => Click on OK Button

Click on OK Button

Linked Server

Step 1:- GO TO

MS SQL Server => Expand Server Objects => Expand Linked Servers => Right on Linked servers => Click on New Linked Server... => Fill Left Side General Tab details => Fill Left Side Security Tab details => Click on OK Button

Step 2:- GO TO

Expand Providers => Right Click MSDASQL => Select Properties => Select following Checkbox => Click OK Button

Step 3:- Step by Step

MS SQL Server

Expand Server Objects

Expand Linked Servers

Right on Linked servers

Click on New Linked Server...

Fill Left Side General Tab details

Fill Left Side Security Tab details => Click on OK Button

Expand Providers

Right Click MSDASQL => Select Properties

Select following Checkbox => Click OK Button

QUERY

Note:-

1.         Your DNS Name and Linked Server Name must be same.

2.         Step is based on local server due to which, while creating the DSN password field is empty. You can use MY SQL password if you given.

3.         After establishing a linked connection use the OPENQUERY to get data or query.

  

Example: - OPENQUERY ( linked_server ,'query' )

SELECT * FROM OPENQUERY(SFA_MYSQL,'SELECT * FROM INFORMATION_SCHEMA.TABLES LIMIT 10')