accessing MySQL from SQL Server 2008 (RPM)

In a company where multiple database environments are often deployed, it can be tedious for developers to switch between different databases often, and this article describes how to manipulate MySQL databases from SQL Server.

Database test Environment

1. SQL Server 2008

2. MySQL 5.1.36
Database:test
Table:testtable

Create a MySQL test table

CREATE TABLE  (   int(one)  varchar(oneNULL)  

Create linked Server

Below to create a link to the MySQL interaction (similar to Oracle's Dblink), in SQL Server Manager, right-click the Linked in the server Objects directory Servers tap "New Linked Server":

Linked Server link name

Server Type Select "Other data source"

Provider Select "Microsoft OLE DB Provider for ODBC Drivers"

Product name fills in the MySQL host address

Provider String filled driver={mysql ODBC 5.1 Driver}; Server=servername;database=mydb;
User=myusername; password=mypassword;option=3;

You can also create directly from TSQL:

EXEC Master. dbo.sp_addlinkedserver @server=N ' Mysqllink ', @srvproduct=N ' localhost ', @provider=N ' Msdasql '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 Span style= "Color:gray;" >= n ' root '           

When you're done, you'll see a link in:

Working with MySQL Database

In SQL Server, you operate on MySQL with 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 the read or modify operation to the linked Server. The following are examples:

([LinkedServer]' SELECT * from [remotetable] ') 

To insert the test data into the testtable:

INSERT INTO OpenQuery(Mysqllink,' select * from TestTable ')Select 1,' Peter ', 30 ;

Query data:

From OpenQuery(Mysqllink,' select * from TestTable ');   

To modify the data:

Update OpenQuery(mysqllink,' select * from TestTable ')set name=where id=1; 

Delete data:

Delete from OpenQuery(Mysqllink,' select * from TestTable ')where ID=1;    

Related references

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

Http://www.connectionstrings.com/mysql#p21