Several instances connecting SQL Server to MySQL

MySQL data is linked to SQLServer when you access multiple databases through a program. The following is a summary:

1. Install the mysql-connector-odbc driver;

1. Install the mysql-connector-odbc driver on the SQL Server;

Its 64-bit download address: http://pan.baidu.com/s/1gdq9WyB

During installation, it may be reported

 

You only need to install Microsoft Visual C ++ 2010 Redistributable Package.

The 64-bit installation package download address: http://pan.baidu.com/s/1i3vp9rB

2. Create an ODBC data source after installation;

Choose Control Panel> Administrative Tools> data source (ODBC)> System DSN to add the system data source and mysql ODBC driver.

 

Enter Mysql database information

 

This completes the configuration of the ODBC data source.

2. Create a linked Server on SQL Server data;

In the database "server object"-"link server", create a "link server" and fill in the relevant information;
 

 
 

The format of the access interface string is 'driver = {MySQL ODBC 5.3 Unicode Driver}; Server = 192.168.1.XXX; Database = dasems; User = root; Password = 111111; Option = 3 ;';

Select

 

3. How to connect to a server.

Add data:

Insert into openquery (mysqllink, 'select * from testtable ')
Select 1, 'Peter ', 30;

Delete data:

Delete from openquery (mysqllink, 'select * from testtable ')
Where id = 1;

Modify data:

Update openquery (mysqllink, 'select * from testtable ')

Set name = 'jone' where id = 1;

Query data:

Select * from openquery (MYSQL_LINUX, 'select * from buildinginfo ');




How SQL Server connects to remote MySQL

Recently, I encountered the problem of "how to connect SQL to remote MySQL", and now the problem is finally solved. I posted the method: (my operating system is Win7, and the database is SQL2005 .)

1. Install the mysql odbc driver on the SQL SERVER;

Http://dev.mysql.com/downloads/mirror.php? Id = 376346 (Download address)

2. After installation, choose "management tools"> "ODBC data source"> "system DSN"> "add"> "mysql odbc 3.51 DRIVER"> "complete".

MYSQL connection parameters are displayed.

Data Source Name: Data Source Name

Decription: description.

Server: IP address of the MYSQL Server

Port: MYSQL Port. The default value is 3306. You can change the value.

User: connection account (the account authorized on mysql and corresponding permissions)

Password: Password

Database: Select the connected Database

After entering the information, click test. The prompt box is displayed as shown in the figure.

After successful:

3. Open the SQL database and find the link to the server. you can create a linked server, so I will send the parameter. as shown in the figure, the rest of the general options are not required. OK.

Note: Select "Microsoft ole db Provider for ODBC Drivers" for the access interface ".

Data source: Select the created ODBC interface.

4. Test:

(Query statement)

SELECT * from openquery (MYSQL, 'select * from table ')

(Insert statement)

Insert openquery (MYSQL, 'select * from table') (id, name, password) values (8, 'Zidane ', '2016 ')



Add a MySql linked server under SqlServer

To install mysql's odbc driver on the server of sqlserver, I download mysql-connector-odbc-winx64.zip. After installation, ODBC has the Driver ={ MySQL ODBC 5.3 ANSI Driver} ({MySQL ODBC 5.3 Unicode DRIVER })

-- Query the existing connection server exec sp_linkedservers; -- delete the Connection server exec sp_dropserver 'MySQL _ testdb', 'droplogins' -- execsp_addlinkedserver @ server = 'MySQL _ testdb' in script mode ', @ srvproduct = 'mysql', @ provider = 'msdasql ', @ provstr = 'driver = {MySQL ODBC 5.3 ANSI Driver}; Server = localhost; Database = zkeco_db; User = root; password = 1234; Option = 3; 'go EXEC sp_add1_srvlogin @ rmtsrvname = 'mysqltest', @ useself = 'false', @ rmtuser = 'root', @ rmtpassword = '123 '; GOEXEC sp_addmediaserver @ server = 'mysqltest', @ srvproduct = 'zkeco _ db', @ provider = 'msdasql ', @ provstr = 'driver = {MySQL ODBC 5.3 ansi driver }; SERVER = 127.0.0.1; DATABASE = zkeco_db; UID = root; Password =; PORT = 17770; ', @ datasrc = null go exec sp_add1_srvlogin @ rmtsrvname = 'mysqltest ', @ useself = 'false', @ locallogin = 'sa ', @ rmtuser = 'root', @ rmtpassword = ''Go SELECT * from openquery (MySQL_TestDB, 'select * from userinfo') insert openquery (MySQL_TestDB, 'Select id, parentid, level, title FROM monolithpro_cate ') VALUES ('', '0', '0', '0 ', 'zhangzongqi '); update openquery (MySQL_TestDB, 'Select parentid, level, title FROM monolithpro_cate WHERE id = 320') SET parentid = '2', LEVEL = 3, title = title + 'pct '; delete openquery (MySQL_TestDB, 'Select id FROM monolithpro_cate WHERE id = 100 ');