How to operate MySQL through SQL Server 2008 _mssql2008

Database test Environment
1. SQL Server 2008

2. MySQL 5.1.36
Database:test
Table:testtable

Create a MySQL test table

Copy Code code as follows:

CREATE TABLE ' TestTable ' (
' id ' int (one) DEFAULT NULL,
' Name ' varchar DEFAULT NULL,
' Age ' int (one) DEFAULT NULL
)

Create link Server
To create a link to MySQL interaction (Oracle-like Dblink), in SQL Server Manager, right-click the server Objects directory in linked Servers Click New linked server:

linked server link name
Server Type Selects "Other data source"
Provider Select Microsoft OLE DB Provider for ODBC Drivers
Product name fill in MySQL host address
Provider String fills in Driver={mysql ODBC 5.1 Driver}; Server=servername;database=mydb;
User=myusername; password=mypassword;option=3;

Can also be created directly through TSQL:

Copy Code code as follows:

EXEC Master.dbo.sp_addlinkedserver
@server = N ' Mysqllink ',
@srvproduct = N ' localhost ',
@provider = N ' Msdasql ',
@provstr = N ' driver={mysql ODBC 5.1 Driver}; Server=localhost;
Database=test; User=root; password=root;option=3; '

EXEC Master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N ' Mysqllink ',
@useself = N ' False ',
@locallogin = N ' localhost ',
@rmtuser = N ' root ',
@rmtpassword = N ' root '

When you are done, you will see the links in the following figure:

Operating the MySQL database
In SQL Server, you manipulate MySQL by using the OPENQUERY function, which contains two parameters: the first is the name of the link that was created, and the second is the SQL query, which returns a read or modify operation on linked Server. Examples of the following operations:

OPENQUERY ([LinkedServer], ' SELECT * from [remotetable] ') inserts test data into TestTable:

Insert INTO OPENQUERY (Mysqllink, ' select * from TestTable ')
Select 1, ' Peter ', 30; query data:

SELECT * FROM OPENQUERY (mysqllink, ' select * from TestTable ');

Modify Data:

Copy Code code as follows:

Update OpenQuery (Mysqllink, ' select * from TestTable ')
Set name= ' Jone ' where id=1;

Delete data:

Copy Code code as follows:

Delete from OPENQUERY (Mysqllink, ' select * from TestTable ')
where id=1;

Related reference

http://dev.mysql.com/downloads/mirror.php?id=376346

Http://www.connectionstrings.com/mysql
Author: Gnie