Linked server for a MySQL server from SQL Server

MySQL Connector/ODBC driver
https://dev.mysql.com/downloads/connector/odbc/


from https://www.mssqltips.com/sqlservertip/4577/create-a-linked-server-to-mysql-from-sql-server/

Problem

SQL Server Linked Servers are used to query remote databases and in this tip we will learn how to configure a linked server for a MySQL server from SQL Server.

Solution

Here are the steps to create a linked server to a MySQL server.

First we need to create an ODBC data source to the MySQL server on the box where SQL Server is installed. In my case I have MySQL and SQL Server installed on the same box.

Go to Start > Administrative Tools > Data Sources (ODBC) > System DSN and select Add

Select the MySQL ODBC driver.

Give the data source name of your choice. Input the TCP/IP Server address, port, user and password for the MySQL server and click OK.

Now open SQL Server Management Studio (SSMS) and login to SQL Server.

In the Object Explorer for the SQL Server instance, go to Server Objects and right click and select New > Linked Server...

Give the linked server a name of your choice.

Under the Provider drop down select Microsoft OLEDB Provider for ODBC Drivers.

Input the Product name as MySQL and enter the data source name which was created earlier.

Go to the Security tab and enter the remote login and password (on the bottom) and click OK.

Now test the linked server which was just created and you should be able to see the objects for the MySQL data source (note I hid the names of the tables from my server in the image below).

在 Windows 10 中開啟 ODBC 資料來源管理員

1. 在 [開始] 頁面上輸入 ODBC 資料來源。 「ODBC 資料來源傳統型應用程式」 應該會出現在搜尋選項中。

query sql
SELECT *
FROM OPENQUERY(POWER_DB, 'SELECT * FROM 3egreenserver.groupdemandtable');