Cross-linking between different databases

MSSQL link MySQL Database method:
Prerequisites (System Installation MYODBC, this can be downloaded to the MySQL website)

1. Using TSQL to establish (the success of the establishment does not mean you can query)
Directly using the connection string
EXEC master.dbo.sp_addlinkedserver @server = N ' MYSQL1 ', @srvproduct =n ' MySQL ', @provider =n ' msdasql ', @provstr =n ' DRIVER ={mysql ODBC 3.51 Driver}; server=127.0.0.1; Database=test; User=root; Password=root; Option=3 '
EXEC master.dbo.sp_addlinkedserver @server = N ' MYSQL2 ', @srvproduct =n ' MySQL ', @provider =n ' msdasql ', @provstr =n ' DRIVER ={mysql ODBC 5.3 UNICODE Driver}; server=127.0.0.1; Database=test; User=root; Password=root; Option=3 '
where MySQL ODBC 3.51 Driver mysql ODBC 5.3 UNICODE Driver, this need to "manage"--"ODBC" to view the version of MYODBC on a specific machine

In the ODBC data Source Manager--system DNS establishes a MySQL connection
EXEC master.dbo.sp_addlinkedserver @server = N ' MYSQL3 ', @srvproduct =n ' MySQL ', @provider =n ' msdasql ', @datasrc =n ' Odbc2mysql3 ', @catalog =n ' test '

2. Set up in the MSSQL manager UI
Note (the data source and the connection string can only be used for one, and the two are conflicting, this should be especially noted)

How the data is manipulated when the link is complete (MySQL does not now support four-part names)

The network says MYODBC does not support
SQL link Server link mysql http://bbs.csdn.net/topics/380106204
OLE DB Providers tested with SQL Server http://msdn.microsoft.com/en-us/library/ms187072.aspx

--Insert the record of the TT table in SQL Server into the T1 table of MySQL, this can also insert the specific value.
INSERT OPENQUERY (mysqltest, ' select * from T1 ') select * from TT;
INSERT OPENQUERY (mysqltest, ' select * from T1 ') values (99,99,99);

--Update operation on the T1 table
UPDATE OPENQUERY (mysqltest, ' select value from T1 ') set Value=value +100;

--delete operation on T1 table, do not understand why only one can be deleted at a time,
DELETE OPENQUERY (mysqltest, ' SELECT * from t1 WHERE hour = 99 ');
DELETE OPENQUERY (mysqltest, ' SELECT * from T1 ');

--DELETE OPENQUERY (mysqltest, ' SELECT * from T1 WHERE hour > 2 '); --This is not possible, error
/*
The OLE DB provider "MSDASQL" of the linked server "Mysqltest" returned the message "The key column information is insufficient or incorrect." The update affects multiple lines. "。
Msg 7345, Level 16, State 1, line 1th
The OLE DB provider "MSDASQL" of the linked server "Mysqltest" cannot delete data from the table "select * from T1 where hour > 2". The update operation does not meet the schema requirements.
*/
--query operation on the T1 table
SELECT * FROM OPENQUERY (mysqltest, ' select * from T1 ');

--Write the records of MySQL's T1 table back to MSSQL

INSERT INTO TT select * FROM OPENQUERY (mysqltest, ' select * from T1 ');

Cross-linking between different databases