SQL Server remote link mysql database

First step: Install MySQL ODBC driver

Install MySQL ODBC Driver on the host where SQL Server resides;

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

Find the appropriate version of your operating system to download and install

Step Two: Configure ODBC Data Source

Open Administrative Tools, data sources (ODBC), select System DSN, click Add, select MySQL ODBC 5.3 Unicode Driver, complete

Enter the custom data source name xxx and description, enter the MySQL server IP address/port/username/password and DB instance name you want to connect to, click Test, Success, OK.

Description

Data Source Name: DataSource names, custom

Decription: Description, Custom

IP of the Server:mysql server

User: Connect the account (the account authorized on MySQL, and give the appropriate permission)

Password: Password

Database: Select the linked databases

Port:mysql port, the default is 3306. Depending on the actual MySQL server port, you can change it in the following interface

After filling it out, click Test. If it succeeds, it will jump out of the prompt box.

Step three : Configure database links

Open SQL Server Management Studio, select server objects, right-click linked servers, select new linked server,

General tab, enter a custom server link name, select other data source

Provider item, select Microsoft OLE DB Provider for ODBC Drivers, do not choose the wrong

Enter a product name (custom), data source name (the name of the previous step, XXX)

Security tab, select Establish a connection using this security context, enter the Telnet user name and password

Are you sure!

If there is no error, refresh the "Linked Servers" node, right click on the new link name, select "Test Link", no problem.

Click the Green instance, server object, link server-right--new linked server--popup interface

Attention:

1 Access interface: Microsoft OLE DB Provider for ODBC Drivers

2 data source, which is the ODBC data source test we previously configured, must exist

Other optional, click "OK"

Iv. Use of:

1, OpenQuery Way;

SELECT * from OPENQUERY (dblink name, ' select * from t_table ');

UPDATE OPENQUERY (test, ' SELECT ID from tobj_operate WHERE id = 101 ')

SET name = ' Hello ';

INSERT OPENQUERY (test, ' SELECT ID from tobj_operate ')

VALUES (' Hello ');

DELETE OPENQUERY (test, ' SELECT ID ' from tobj_operate WHERE name = ' Hello ');

2, OpenRowset Way;

Open OPENROWSET First:

exec sp_configure ' show advanced options ', 1
Reconfigure
exec sp_configure ' Ad Hoc distributed Queries ', 1
Reconfigure

Example execution:

SELECT * from OPENROWSET (
' Msdasql ',
' Driver={mysql ODBC 5.3 Unicode DRIVER};
server=111.222.222.111;
PORT=3306;DATABASE=MYSQLDB;
User=root; password=xxxyyyzzz;
Stmt=set names gb2312;
option=2049 ',
' SELECT * from t_table; '
)
Go

Reference:

http://blog.csdn.net/yishengreai/article/details/38230289

Http://blog.sina.com.cn/s/blog_13cc013b50102v5qn.html

SQL Server remote link mysql database