Steps and methods for using SQL Server to operate MySQL

In a variety of database environments, you may often encounter data conversion and mutual operations between different databases. The following describes how to use SQL Server to operate MySQL.

1 preparation before operation 1.1 install the MySQL driver

To operate MySQL on SQL Server, first install the MySQL driver on the server where SQL server is located. MySQL driver installation package can be downloaded on the MySQL official website, I downloaded the installation package name: mysql-connector-odbc-5.1.8-winx64, applicable to 64-bit Windows server. Double-click the installation package and directly [next] the installation is successful. After the installation is successful, enter [odbcad32.exe at runtime to open [ODBC data source Manager] and click [DriverProgram] Option, you can see that [MySQL ODBC 5.1 Driver] has been installed successfully.

1.2 create an ODBC Data Source

After installing the MySQL driver, you can create an ODBC Data Source pointing to the MySQL server on the server where SQL server is located.

In the [ODBC data source Manager], select the [system DSN] tab and click the [add] button.

Select [MySQL ODBC 5.1 Driver] and click [finish].

In the pop-up Configuration box, enter the data source name, MySQL Server IP address, port, user name, and password, and click [OK]. The ODBC data source is created successfully.

1.3 create a linked server

You can create a linked server by using the wizard in SQL Server Management studio or by using SQL statements.

1.3.1 use the Wizard to create a linked server

Connect to SQL Server in SQL Server Management studio, right-click [Server Object]-> [linked server], and choose [New Connection server (n)…].

Enter the custom name of the linked server in the [linked server] box, select [other data sources] for the [server type], and select "Microsoft ole db provider for ODBC drivers" for the [access interface ", the [product name] is the same as the name of the linked server. For the [Data Source], enter the ODBC data source you just created and click [OK]. The linked server is successfully created.

In fact, you can directly create a connection server without creating an ODBC Data Source. You only need to leave the [Data Source] blank and enter the link string in the [access interface string ].

"Driver = {MySQL ODBC 5.1 Driver}; server = 192.168.0.21; database = test; user = root; Password = rootpassword; option = 3. For example:

1.3.2 use SQL statements to create a linked server

You can use the following SQL statement to create a linked server, where the ODBC data source is used:

Exec master. DBO. sp_addmediaserver

@ Server = n'testmysql', -- Link server name

@ Srvproduct = n'testmysql', -- product name

@ Provider = n'msdasql ', -- access interface

@ Datasrc = n' testserver' -- Data Source Name

 

Exec master. DBO. sp_add1_srvlogin @ rmtsrvname = n'testmysql', @ useself = n'false', @ locallogin = NULL, @ rmtuser = NULL, @ rmtpassword = NULL

Go

 

You can also use the following SQL statement to create a connection server, where the ODBC data source is not used and the access interface string is used:

Exec master. DBO. sp_addmediaserver

@ Server = n'testmysql', -- Link server name

@ Srvproduct = n'testmysql', -- product name

@ Provider = n'msdasql ', -- access interface

@ Provstr = n' driver = {MySQL ODBC 5.1 Driver}; server = 192.168.0.21; database = test; user = root; Password = rootpassword; option = 3; '-- access interface string

Exec master. DBO. sp_add1_srvlogin @ rmtsrvname = n 'testmysql', @ useself = n 'true', @ locallogin = NULL, @ rmtuser = NULL, @ rmtpassword = NULL

Go

2. Use SQL Server to operate MySQL

The structure of the testtable table in MySQL database test is as follows:

 

To operate the table, follow these steps:

2.1 query Mysql Data

Select * From openquery (testmysql, 'select * from test. testtable ');

2.2 Insert data into the MySQL table

Insert into openquery (testmysql, 'select * from test. testtable ')

Select 1, 'testname ';

2.3 Delete data from a MySQL table

Delete from openquery (testmysql, 'select * from test. testtable ');

2.4 modify data in the MySQL table

Update openquery (testmysql, 'select * from test. testtable ')

Set testname = 'abcde' where testid = 1;

 

From http://blog.csdn.net/yongsheng0550/article/details/6598252